top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

Retrieve most recent of multiple rows

0 votes

I have a table which looks like this:

answer_id  q_id  answer  qscore_id  answer_timestamp
1          10    Male    3          1363091016
2          10    Male    3          1363091017
3          11    Male    3          1363091018
4          10    Male    3          1363091019
5          11    Male    3          1363091020
6          12    Male    3          1363091020
7          11    Male    3          1363091025

So I have multiple answers for the same questions (q_id). I want to be able to retrieve only ONE answer per question and that be the most recent answer.
There should be THREE rows returned, which are all the most recent answered for that q_id:

4          10    Male    3          1363091019
6          12    Male    3          1363091020
7          11    Male    3          1363091020
posted Mar 13, 2013 by Salil Agrawal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes
Best answer

this is the data i tested on

      AID        QID ANS             SCRID ATIMESTAMP
---------- ---------- ---------- ---------- ---------------------------------------------------------------------------

     1         10 male                3 13-MAR-13 PM
     2         10 male                3 13-MAR-13 PM
     3         11 male                3 13-MAR-13 PM
     4         10 male                3 13-MAR-13 PM
     5         11 male                3 13-MAR-13 PM
     6         12 male                3 13-MAR-13 PM
     7         11 male                3 13-MAR-13 PM

7 rows selected.

select qid,max(atimestamp) from kkk where qid in (select distinct qid from kkk) group by qid;

---------- ---------------------------------------------------------------------------
    11 13-MAR-13 PM
    10 13-MAR-13 PM
    12 13-MAR-13 PM
answer Mar 13, 2013 by anonymous
0 votes

select * from tab where anwer_timestamp in (select max(anwer_timestamp) from tab where q_id in (select distinct q_id from tab) group by q_id);

answer Mar 13, 2013 by anonymous
Similar Questions
0 votes

I'm trying to create a foreign key on two columns but getting error...

Here's what I tried:

  col1 INT NOT NULL,
  col2 INT NOT NULL, 
  CONSTRAINT fk FOREIGN KEY (col1, col2)
                REFERENCES test1(ID, ID)

But I get ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)

0 votes

I am running My-SQL in cluster mode with two machine. Want to know if mysql database get corrupted on one of the machine will it force the corruption on the other machine too or in this case sync between two mysql instances will stop after the corruption.

0 votes

To source sqldump i can use the source command, but if I need to do the same stuff using command line without going to the sqlpromt, can I achieve that.

0 votes

My site is using mysql and PHP, now for the scale purpose want to introduce mysql-cluster. Few questions are -
1. Do I need to change any code which is written in PHP.
2. What are the steps to convert mysql to mysql-cluster.

Appreciate the help.

Contact Us
+91 9880187415
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Karnataka INDIA.