MySQL select fields from row where criteria met, and field from row above
up vote
0
down vote
favorite
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 Sisson...