MIN and MAX in the same query

[Orthodox73]

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!

[steelshark]

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

[Orthodox73]

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

[smantscheff]

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;