group by highest point and shortest time

Question

I would like to query the ranking for the on highest point, if the point is the same, check the time to complete is the shortest. my table is

 +-------------------------------------------------+
 | | id | user_id | point | finishtime  | week  | |
 +-------------------------------------------------+
 | | 1  | G1      | 1560  |    55       |    1  | |
 | | 2  | G1      | 1560  |    43       |    1  | |
 | | 3  | G1      | 1530  |    55       |    1  | |
 | | 4  | G2      | 1760  |    45       |    1  | |
 | | 5  | G3      | 1760  |    46       |    1  | |
 | | 6  | G3      | 1330  |    25       |    2  | |
 | | 7  | G4      | 360   |    65       |    1  | |
 | | 8  | G2      | 1760  |    50       |    1  | |

Expected result is

 +-------------------------------------------------+
 | | id | user_id | point | finishtime  | week  | |
 +-------------------------------------------------+
 | | 4  | G2      | 1760  |    45       |    1  | |
 | | 5  | G3      | 1760  |    46       |    1  | |
 | | 2  | G1      | 1560  |    43       |    1  | |
 | | 7  | G4      | 360   |    65       |    1  | |
 | | 6  | G3      | 1330  |    25       |    2  | |

I tried select max for the point, but it won't take the shortest finishtime.

I need the result group by unique user_id, get the order by highest point, and shortest finishtime.

Am I need to use if else statement?

(deleted an answer) - why you're keeping only two rows with `point=1760` ? I.e. what is the criteria? — Alma Do, Oct 10 '13 at 06:30
some people can score the same point, when it is same point, then we see on the second criteria which is shortest time. who get the shortest time, who get high ranking. It is possible to same user or diff user — Shiro, Oct 10 '13 at 06:37

score 1 · Accepted Answer · answered Oct 10 '13 at 06:42

Here is one possible answer:

SELECT t1.* FROM t t1
JOIN (
  SELECT t1.user_id, t1.week, t1.point, min(t1.finishtime) ft FROM t t1
  LEFT JOIN t t2
  ON t1.week = t2.week AND t1.user_id = t2.user_id AND t1.point < t2.point
  WHERE t2.point IS NULL
  GROUP BY t1.user_id, t1.week, t1.point
) t2 ON t1.user_id = t2.user_id AND t1.week = t2.week AND
        t1.point = t2.point AND t1.finishtime = t2.ft
ORDER BY t1.week, t1.point DESC, t1.finishtime

Results:

| ID | USER_ID | POINT | FINISHTIME | WEEK |
|----|---------|-------|------------|------|
|  4 |      G2 |  1760 |         45 |    1 |
|  5 |      G3 |  1760 |         46 |    1 |
|  2 |      G1 |  1560 |         43 |    1 |
|  7 |      G4 |   360 |         65 |    1 |
|  6 |      G3 |  1330 |         25 |    2 |

Fiddle here.

It is basically a double greatest-n-per-group issue, as you need to first get the ones for the top points and then the ones for the minimum finishtime.

An alternative solution would be to go for the double group by... but that will involve a third nesting level and tried to avoid it, so went for the left join solution.

Thank you so much for your answer, it WORKS! and it is such a complex logic need to think about it.. You are really pro dude — Shiro, Oct 11 '13 at 08:08
Hi, do you mind to explain a bit regarding, why you need to have two t1? I not really get your logic for the sql statement. Appreciate for your hard work! — Shiro, Oct 11 '13 at 14:56
You don't actually need the 2 `t1` you can name one of the two `t3`. As you can see, the outer reference to t1 never gets into the `JOIN (x)`. The `t1` reference in the join is only used in there, that's why there is no naming issue. Short clarification: replace all `t1` references inside the `JOIN` by `t3` and it should work the same way. — Mosty Mostacho, Oct 11 '13 at 18:21

score 0 · Answer 2 · answered Oct 10 '13 at 09:01

Here's a way to do it using just JOINS

 SELECT
    DISTINCT
    t1.user_id,
    max(t2.point),
    min(t2.finishtime),
    min(t1.week)
 FROM
    t t1
    JOIN t t2 ON ( t1.user_id = t2.user_id )
 GROUP BY 
    t1.user_id
 HAVING
    MAX( t1.point ) = MAX( t2.point )
    AND MIN( t1.finishtime ) = MIN( t2.finishtime )
 ORDER BY 
    t1.user_id

Results:

| USER_ID | MAX(T2.POINT) | MIN(T2.FINISHTIME) | MIN(T1.WEEK) |
|---------|---------------|--------------------|--------------|
|      G1 |          1560 |                 43 |            1 |
|      G2 |          1760 |                 45 |            1 |
|      G3 |          1760 |                 25 |            1 |
|      G4 |           360 |                 65 |            1 |

Thanks to Mosty for the SQLFIDDLE

Your answer is not what I expected. I would like to compare the point first, then followed same row of the time of the record. I had put the result there. — Shiro, Oct 10 '13 at 15:58

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

0

SELECT id, user_id, point, finishtime, week
FROM (  SELECT id, user_id, point, finishtime, week
        FROM table_name
        ORDER BY point DESC, finishtime ASC) AS h
GROUP BY user_id, week
ORDER BY week ASC

This is probably one of the shortest ways to do this in, using an undocumented behavior. Read more below.

edited May 23 '17 at 11:57

Community

1
1

answered Oct 10 '13 at 09:36

Robin Castlin

10,956
1
28
44

your sql output not taking the highest score and the min. time. But both record can be under diff raw. What I need is it need to be same row. – Shiro Oct 11 '13 at 08:07
Try now. I might've been thinking wrong the first time around. – Robin Castlin Oct 11 '13 at 09:27

group by highest point and shortest time

3 Answers3