2

The scenario is based around a race event (running/jogging)

Is there a way of getting a position of a person by using their race results. For example the quickest person to finish a race would be in a seperate table of race position with the value of 1. The second quickest person would get a result of 2, and so on. Just by using the race result of that individual (Data Type - Time).

2

You can use a variable and CROSS JOIN it with your table. You should increment the variable value for each returned record.

Keep in mind you need to set the correct order on your subquery.

order by duration asc

I've set up the next example:

drop table if exists course;
create table if not exists course(id int, duration time);
insert into course values
(1, '01:20:00'),
(2, '01:21:02'),
(3, '01:19:02'),
(4, '01:22:02'),
(5, '01:17:05'),
(6, '01:23:02'),
(7, '01:12:02'),
(8, '01:16:02'),
(9, '01:15:02');


select id, duration, @position := @position +  1 as position
from
       (select @position := 0) x,
       (select id, duration
        from course
        order by duration asc) y;


drop table if exists course;

And this is the result:

| id | duration | position |
|---:|---------:|----------|
| 7  | 01:12:02 | 1        |
| 9  | 01:15:02 | 2        |
| 8  | 01:16:02 | 3        |
| 5  | 01:17:05 | 4        |
| 3  | 01:19:02 | 5        |
| 1  | 01:20:00 | 6        |
| 2  | 01:21:02 | 7        |
| 4  | 01:22:02 | 8        |
| 6  | 01:23:02 | 9        |

Rextester here.

  • Amazing stuff! Thankyou, very easily laid out too! – HeadsR'Us Apr 12 '17 at 14:22
  • I'm glad to be able to help. – McNets Apr 12 '17 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.