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?

The message says it all really, you're not selecting `Time` (just QID) but you try to order by it, which is not allowed with `DISTINCT` queries. — Joachim Isaksson, Feb 27 '16 at 18:05
Which time do you want to order by? If you for example have 2 rows, `{QID=1, Time=21:00:00}` and `{QID=1, Time=22:00:00}`, the resulting row with your query would be `{QID=1}` but which of the two times should it order by? — Joachim Isaksson, Feb 27 '16 at 18:09
Yes, but if the only QID is DISTINCT, Time is not. That is, for each QID you have multiple times and need to decide which of them to order by. — Joachim Isaksson, Feb 27 '16 at 18:16
I want to get all fields from the table but if i have 2 columns with same Qid i want to get the last... — user11001, Feb 27 '16 at 18:19

Pரதீப் · Answer 1 · 2016-02-27T18:45:51.897

2

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

edited Feb 27 '16 at 18:45

answered Feb 27 '16 at 18:08

Pரதீப்

91,748
19
131
172

2

As far as I know, `DISTINCT` does not guarantee to keep any order from its input (the subquery) – Joachim Isaksson Feb 27 '16 at 18:12
it doesnt work for me.. so what can i do if i want to order it and to distinct? – user11001 Feb 27 '16 at 18:13

Joachim Isaksson · Answer 2 · 2016-02-27T18:28:37.053

1

For your requirement you don't need DISTINCT, you just need to show rows where there exists no newer row with the same QID;

SELECT * FROM answer
WHERE NOT EXISTS (
  SELECT 1 FROM answer a
  WHERE answer.qid = a.qid 
    AND answer.time < a.time
)

An SQLfiddle to test with.

edited Feb 27 '16 at 18:28

answered Feb 27 '16 at 18:21

Joachim Isaksson

176,943
25
281
294

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

Maybe can help you THIS LINK

If you want use order and distinct use group by.

edited May 23 '17 at 11:50

Community

1
1

answered Feb 27 '16 at 18:08

starko

1,150
11
26

DISTINCT SQL Command

3 Answers3