Wsrtjtyk

I have a data table of results where I would like to select information which is mostly held against the record relating to the second-place finisher.

So, for example, the location of the race, the date of the race, and the time deficit to the winner.

But what I would like to show in my query is the winner followed by their winning margin, which is actually stored against the record relating to the second-place finisher.

For example:

id | race_id | result | Entrant       | Time

---|---------|--------|---------------|------

0  |   001   |    1   | Huw Edwards   | 54:78.491

1  |   001   |    2   | Fiona Bruce   | 0.250

2  |   001   |    3   | Sophie Raworth| 3.114

3  |   001   |    4   | George Alagiah| 6.756

4  |   001   |    5   | Mishal Husain | 42.452

5  |   001   |    6   | Peter Sissons | 58.004

6  |   002   |    1   | Fiona Bruce   | 43:08.549

7  |   002   |    2   | Sophie Raworth| 5.439

8  |   002   |    3   | Mishal Husain | 5.444

9  |   002   |    4   | Huw Edwards   | 6.007

10 |   002   |    5   | Peter Sissons | 13.000

11 |   002   |    6   | George Alagiah| 2:12.782

I have tried a query such as this (not working as desired):

SELECT r.id, r.entrant, r.time 

FROM races as r 

LEFT JOIN races as r1 

ON r1.id > r.id 

WHERE r.result = "2"

I'd like to end up with a result like the following, because it shows the winning margin that Huw Edwards enjoyed.

id | race_id | entrant     | time

---|---------|-------------|-------

 1 |  001    | Huw Edwards | 0.250

 7 |  002    | Fiona Bruce | 5.439

Here is my table schema:

CREATE TABLE `races` (

 `series` varchar(256) COLLATE utf8_unicode_ci NOT NULL,

 `year` int(11) NOT NULL,

 `round` int(11) NOT NULL,

 `date` date NOT NULL,

 `race_id` text COLLATE utf8_unicode_ci NOT NULL,

 `track` varchar(256) COLLATE utf8_unicode_ci NOT NULL,

 `result` int(11) NOT NULL,

 `number` int(11) NOT NULL,

 `class` varchar(11) COLLATE utf8_unicode_ci NOT NULL,

 `class_pos` int(11) NOT NULL,

 `runner` varchar(256) COLLATE utf8_unicode_ci NOT NULL,

 `runner_id` int(11) NOT NULL,

 `runner2` varchar(256) COLLATE utf8_unicode_ci NOT NULL,

 `runner_id2` int(11) NOT NULL,

 `entrant` varchar(256) COLLATE utf8_unicode_ci NOT NULL,

 `laps` int(11) NOT NULL,

 `time` varchar(256) COLLATE utf8_unicode_ci NOT NULL,

 `best` text COLLATE utf8_unicode_ci NOT NULL,

 `qual` int(11) NOT NULL,

 `marque` varchar(256) COLLATE utf8_unicode_ci NOT NULL,

 `id` int(11) NOT NULL AUTO_INCREMENT,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=43021 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

It is not 100% clear what do you want to get.

Please provide more examples with some other expected results.
So far, to give you some ideas of how you can get data you need:

http://sqlfiddle.com/#!9/7eb44f/2

SELECT r.id,

      r.race_id,

       r.entrant,

       r1.entrant as Winner, 

       r1.time WinnerTime,

       r.time 

FROM races as r 

LEFT JOIN races as r1 

ON r1.result = 1 

   AND r1.race_id = r.race_id

WHERE r.result = 2

UPDATE Your table schema is terrible. to make my query faster for your table you should at least change race_id column type integer (or varchar) but not text! And you need then to create an index over result and race_id columns:

ALTER TABLE races MODIFY race_id INTEGER; 



CREATE INDEX result_race_id ON races (result, race_id); 

Your query looks pretty close, you just want to use the correct aliases - so you want r1.time and r1.result instead or r.time and r.result

SELECT r.id, r.entrant, r1.time 

FROM races as r 

LEFT JOIN races as r1 

ON r1.id > r.id 

WHERE r1.result = "2"

You can use a correlated query:

select id, entrant, (

    select time

    from races x

    where race_id = races.race_id AND result = 2

) as win_margin

from races

where result = 1