CODEWITHSUNDEEP
mysqlsqlrows
Bhav
Bhav

Reputation: 2195

MySQL - if row is duplicate, return only the first one

I have a MySQL table "results" which has the following fields: 

id (PK, AI), user_id (FK), date, score, time

I want to be able to query this table so that it sorts and returns the fields in order of score (descending order) followed by time (ascending order). So something like:

SELECT * FROM results ORDER BY score DESC, time ASC.

However, after this sorting, if more than one row has the same user_id, I only want to include the highest row.

How would I do this?

Upvotes: 0

Views: 430

Answers (3)

Bhav
Bhav

Reputation: 2195

I've managed to get something working at the moment.

SELECT user_id, score, time 
FROM results T
WHERE T.score = (
SELECT MAX(T2.score)
FROM results T2
WHERE T2.user_id = T.user_id
)
ORDER BY score DESC, time ASC;

Upvotes: 0

Martin Seitl
Martin Seitl

Reputation: 658

My suggestion: SELECT user_id, max(score), time FROM results GROUP BY user_id ORDER BY score DESC;

Select id and highest score per user_id via max() and Group By. Then order the records by score descending.

EDIT: If you need the time for the user-score and there is only one entry with the same score you can use a subselect to get this time:

SELECT user_id, max(score), (
    SELECT max(time) 
    FROM results AS r2 
    WHERE r2.user_id = r1.user_id
        AND r2.score = max(r1.score)
) AS time 
FROM results AS r1
GROUP BY user_id 
ORDER BY score DESC;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this with not exists:

SELECT *
FROM results r
WHERE NOT EXISTS (select 1 from results r2 where r2.user_id = r.user_id and r2.id > r.id)
ORDER BY score DESC;

This will work best with an index on results(user_id, id).

Upvotes: 1

Related Questions