0

Hello,

I have table with test results. Every user have 3 records in the table RESULTS

Table RESULTS contains fields USERID (int), TEST_POINTS (int), TEST_TIME (int);

I need query to sort up results, but only the best result per user.
Best result for user is one with MAX points for less (MIN) time. So if there are two or more users with the same POINTS, the better is one with smaller time.
Sorry for my bad english and tnx!

3
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by smantscheff
0

SELECT MAX(TEST_POINTS) FROM RESULTS where test_time <= (select MIN(TEST_TIME) FROM RESULTS)
I'm not 100% sure about this query , but it looks the way to go in my eyes

0

Tnx steelshark, but that query is not solution. It has no result...

0

Always provide a full test case.

drop table if exists results;
create table results (user_id integer, test_points integer, test_time integer);
insert into results values
(1,1,1),
(1,3,2),
(1,4,2),
(2,1,5),
(2,4,1),
(2,4,0),
(3,1,1),
(3,2,2),
(3,2,1);

/* expected results :
2 4 0
1 4 2
3 2 1
*/
select user_id, test_points, min(test_time) tt
from results a
where test_points = (select max(test_points) from results b where b.user_id=a.user_id)
group by a.user_id
order by test_points desc, tt asc;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.