CODEWITHSUNDEEP
mysqlsql
The Time
The Time

Reputation: 737

Find the previous and after raw in mysql

I have a stop table, after finding the stop name I want to find the previous and after data name, lat, longi of this name.

CREATE TABLE IF NOT EXISTS stops
stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
name varchar(30) NOT NULL, 
lat double(10,6) NOT NULL, 
longi double(10,6)NOT NULL)

enter image description here

For example if the name is TEST I want to get the name, lat and longi of ABC and sky. It should even work when there is difference between the stop_id like 2,5,7,12

I appreciate any help.

Upvotes: 0

Views: 81

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use variables to achieve what you want:

SELECT stop_id, name, lat, longi, rn
FROM (
   SELECT stop_id, name, lat, longi,
          @r:=@r+1 AS rn
   FROM stops, (SELECT @r:=0) var
   ORDER BY stop_id, name ) s
WHERE name != 'TEST' AND
      rn >= (SELECT row_number
             FROM (
               SELECT name, @row_number:=@row_number+1 AS row_number
               FROM stops, (SELECT @row_number:=0) var
               ORDER BY stop_id, name ) s
               WHERE name = 'TEST' ) - 1     
ORDER BY stop_id LIMIT 2

Demo here

This query:

SELECT name, @row_number:=@row_number+1 AS row_number
FROM stops, (SELECT @row_number:=0) var
ORDER BY stop_id, name

is used twice to simulate ROW_NUMBER window function not available in MySQL. Using it we can identify 'row number' of record having name = 'TEST' and use this info to get the records we want.

Upvotes: 1

Mojtaba Rezaeian
Mojtaba Rezaeian

Reputation: 8736

as @Gordon-Linoff mentioned self joins is a good trick to achieve this:

SELECT ss . * , nex.stop_id AS nex_id, nex.name AS nex_name, nex.lat AS nex_lat, nex.longi AS nex_longi
FROM (
    SELECT s . * , pre.stop_id AS pre_id, pre.name AS pre_name, pre.lat AS pre_lat, pre.longi AS pre_longi
    FROM stops s
    LEFT JOIN stops pre ON s.stop_id = pre.stop_id + 1) AS ss
LEFT JOIN stops nex ON ss.stop_id = nex.stop_id - 1
WHERE (ss.name LIKE 'TEST')
LIMIT 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can get the ids of the previous and next stops using correlated subqueries, assuming that these are the ones with adjacent ids:

select s.*,
       (select s2.stop_id
        from stops s2
        where s2.stop_id < s.stop_id
        order by s2.stop_id desc
        limit 1
       ) as prev_stop_id,
       (select s2.stop_id
        from stops s2
        where s2.stop_id > s.stop_id
        order by s2.stop_id ASC
        limit 1
       ) as next_stop_id
from stops s;

You can then use join to bring in additional information.

It is tempting to use joins for getting the prev/next stops, such as:

select  s.*, prevstop.*
from stops s left join
     stops prevstop
     on s.stop_id = prevstop.stop_id + 1;

However, this assumes that there are no gaps in the values, and that assumption may not be true.

Upvotes: 1

Related Questions