CODEWITHSUNDEEP
mysql
Shiro
Shiro

Reputation: 7554

group by highest point and shortest time

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?

Upvotes: 2

Views: 110

Answers (3)

Robin Castlin
Robin Castlin

Reputation: 10996

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.

Upvotes: 0

Akash
Akash

Reputation: 5012

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

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43484

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.

Upvotes: 1

Related Questions