Select surrounding rows php/mysql

Question

I want to select a group of rows around a row matched on conditions, ie select a row and then return 5 records before and after it.

More info as requested (sorry bout that!)

Ok, so the table records lap times from a race. Laps are related to Users (users have many laps). I want to find the best lap time for a User then x number of best laps from UNIQUE users above AND below the required User's best lap.

Is this possible?

Answer 1

Given the very sparse data, I can only provide a very generic solution. Think of it as inspiration:

SELECT
    id,
    fieldX,
    ABS(id - <id-to-select>) AS distance
FROM
    table
WHERE
    ABS(id - <id-to-select>) < 5
ORDER BY
    distance DESC

This method, however, does not guarantee that a row with id = <id-to-select> is returned. It just returns the row "nearest" to this ID.

Again, this is only meant as a source of inspiration.

Answer 2

So I'm going to assume that you want to sort by lap times.

SELECT `lap_time`, `uid` 
FROM `table`
WHERE `uid` = x AND `lapnum` = y

This will get the row for the last lap. Lets say its stored as an INT and is in seconds. And the result is 120.

Now select all the lap times quicker.

SELECT `lap_time`, `uid` 
FROM `table`
WHERE `laptime` =< 120
GROUP BY `uid`
ORDER BY `laptime` DESC
LIMIT 5 

And lastly select all the lap times slower.

SELECT `lap_time`, `uid` 
FROM `table`
WHERE `laptime` >= 120
GROUP BY `uid`
ORDER BY `laptime` ASC
LIMIT 5 

And that will give you all 11 rows you need!

Answer 3

This took quite a bit of hacking, but it appears to be working on my end.

SELECT *
FROM `lap_times`
WHERE `id` IN (
    SELECT * FROM (
        SELECT lt1.id
        FROM `lap_times` lt1
        WHERE lt1.time = (SELECT MIN(lt2.time) FROM `lap_times` lt2 WHERE lt2.user_id = lt1.user_id)
        AND lt1.time <= (SELECT MIN(lt3.time) FROM `lap_times` lt3 WHERE lt3.user_id = 2)
        AND lt1.user_id != 2
        ORDER BY lt1.time ASC
        LIMIT 5
    ) AS `tbl1`
) OR `id` = (
    SELECT `id`
    FROM `lap_times`
    WHERE `user_id` = 2
    ORDER BY `time` ASC
    LIMIT 1
) OR `id` IN (
    SELECT * FROM (
        SELECT lt1.id
        FROM `lap_times` lt1
        WHERE lt1.time = (SELECT MIN(lt2.time) FROM `lap_times` lt2 WHERE lt2.user_id = lt1.user_id)
        AND lt1.time >= (SELECT MIN(lt3.time) FROM `lap_times` lt3 WHERE lt3.user_id = 2)
        AND lt1.user_id != 2
        ORDER BY lt1.time ASC
        LIMIT 5
    ) AS `tbl2`
)
ORDER BY `time` ASC;

Answer 4

This is really just a correction to PHP-Steven's answer but I don't have enough rep to make a comment.

SELECT `lap_time`, `uid` 
FROM `table` t1
WHERE `lap_time` =< 120
AND NOT EXISTS (SELECT 1 FROM `table`
                 WHERE `uid` = t1.`uid` AND `lap_time` > t1.`lap_time` AND `lap_time` < 120)
ORDER BY `lap_time` DESC
LIMIT 5

and

SELECT `lap_time`, `uid` 
FROM `table` t1
WHERE `lap_time` > 120
AND NOT EXISTS (SELECT 1 FROM `table`
                 WHERE `uid` = t1.`uid` AND `lap_time` < t1.`lap_time` AND `lap_time` > 120)
ORDER BY `lap_time` DESC
LIMIT 5

The only difference is the NOT EXISTS clauses which serve to eliminate secondary lap times by the same user. Otherwise, you get the next slowest and fastest lap_times, but not the next slowest/fastest unique users.

Answer 5

It should be something like:

lap_time = column with lap times (assumed to be an int or numeric)
laps = table containing lap times
CURRENT_LAP_TIME = the lap time in question

then

SELECT lap_time FROM laps
WHERE lap_time < CURRENT_LAP_TIME
ORDER BY lap_time DESC LIMIT 5
UNION
SELECT lap_time FROM laps
WHERE lap_time >= CURRENT_LAP_TIME
ORDER BY lap_time LIMIT 6

You can wrap the whole thing in another SELECT statement and do an ORDER BY if you want it in a specific order. (I think there's a better way to reverse the first SELECT statement, but I can't think of it off the top of my head.)