DISTINCT SQL Command

Question

Update:

qid = 1, nick=aa, value=13, time= 20:00:01
qid = 1, nick=bb, value=45, time= 20:00:50
qid = 2, nick=cc, value=77, time= 20:30:50

expected:
qid = 1, nick=bb, value=45, time= 20:00:50
qid = 2, nick=cc, value=77, time= 20:30:50

i trying to execute this line:

SELECT DISTINCT QID FROM "USERNAME"."ANSWER" WHERE Nickname =? ORDER BY Time DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

And I get this error msg:

The ORDER BY clause may not contain column 'TIME', since the query specifies DISTINCT and that column does not appear in the query result.

What would be the problem?

Answer 1

As stated in error message you cannot use columns in distinct select list when the column is not present in Order by

select * from 
(
select row_number()over(partition by qid Order by Time desc) as Rn,*
From yourtable
) A
Where RN =1 

Or If your database does not support ROW_NUMBER then use this

SELECT a.* 
FROM   yourtable a 
       INNER JOIN (SELECT qid, 
                          Max(time) AS time 
                   FROM   yourtable 
                   GROUP  BY qid) b 
               ON a.qid = b.qid 
                  AND a.time = b.time