sql - 不同的 SQL 命令

Question

更新:

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

我试图执行这一行:

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

我收到此错误消息:

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

会出现什么问题？

Answer 1

如错误消息中所述，当 Order by 中不存在列时，您无法使用 distinct 选择列表中的列

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

或者，如果您的数据库不支持ROW_NUMBER，则使用此

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