1

I have table like this :

+-------------+---------------------+---------------------+--------+
| id  | name  |       start_time    |        end_time     | score  |
+-------------+---------------------+---------------------+--------+
| 1   | Jon   | 2017-10-19 01:00:00 | 2017-10-19 01:20:00 |  20    |
| 2   | Jane  | 2017-10-19 01:15:00 | 2017-10-19 01:30:00 |  20    |
| 3   | Fred  | 2017-10-19 01:00:25 | 2017-10-19 03:10:35 |  80    |
| 4   | July  | 2017-10-19 01:12:28 | 2017-10-19 01:16:35 |  10    |
+-------------+---------------------+---------------------+--------+

I want this:

+-------------+---------------------+---------------------+--------+-------+
| id  | name  |       start_time    |        end_time     | score  |  rank |
+-------------+---------------------+---------------------+--------+-------+
| 3   | Fred  | 2017-10-19 01:00:25 | 2017-10-19 03:10:35 |  80    |   1   |
| 2   | Jane  | 2017-10-19 01:15:00 | 2017-10-19 01:30:00 |  20    |   2   |
| 1   | Jon   | 2017-10-19 01:00:00 | 2017-10-19 01:20:00 |  20    |   3   |
| 4   | July  | 2017-10-19 01:12:28 | 2017-10-19 01:16:35 |  10    |   4   |
+-------------+---------------------+---------------------+--------+-------+

Basically, I want order by score. If the scores are equal, then order by lowest duration time. this is different from ordering just by score , it should also calculate duration from start_time and end_timer

nO_sPeaK
  • 25
  • 7
  • Id's shouldn't change then. but this is just an `SELECT * FROM TBL order by score desc, end_time asc` What rdbms & version is this? If you want a "rank" column the implementation would vary if it was mySQL vs SQL-Server. – xQbert Oct 19 '17 at 15:28
  • i fixed id , u cant just say end_time asc , because as u can see "JANE" has higher end_time but result 15 min , "JON" time is 20 min – nO_sPeaK Oct 19 '17 at 15:35
  • MYSQL version 5.6 – nO_sPeaK Oct 19 '17 at 15:36
  • 2
    Go on. Try something. – Strawberry Oct 19 '17 at 15:36
  • so you want to order by duration not time subtract the two? – xQbert Oct 19 '17 at 15:38
  • @xQbert yes exactly – nO_sPeaK Oct 19 '17 at 15:39
  • Then you just need to order by subtracting the two. `ORDER BY score desc, end_time-start_Time asc;` the rank is a function of a user variable simulating a row_number. 1/2 the battle is phrasing the question correctly. In doing so you almost have the answer yourself. You want to order the scores in a decending fashion with ties going to teh Id's with the shorter duration (end_time-Start_time) and then rank those results. in sequential order. – xQbert Oct 19 '17 at 15:43
  • [DEMO](http://sqlfiddle.com/#!9/194e1f/16) - Not sure why this question is marked as duplicate as the order by issue is not solved. – Deja Oct 19 '17 at 15:45
  • Because the answer is the same. Just because there are a different number of expressions in the `order by`, or the variable names are different doesn't make this a unique question. – jmarkmurphy Oct 19 '17 at 18:00
  • thx it works now – nO_sPeaK Oct 19 '17 at 18:16

1 Answers1

1

DEMO:

  1. One can use user variables to simulate a row_number to give us a rank.
  2. to get the duration difference simply subtract the times. end - start
  3. we can then sort by score descending and duration ascending.

.

SELECT A.*, @RN:=@RN+1 as Rank
FROM SO46833505_TBL  A
CROSS JOIN  (Select @RN:=0) Z
ORDER BY  score desc, end_time-Start_Time asc;
xQbert
  • 34,733
  • 2
  • 41
  • 62