CODEWITHSUNDEEP
mysqloptimization
Roman Losev
Roman Losev

Reputation: 1941

Select a row along with its next and previous rows

Situation:

At the moment I have 3 queries:

  1. First - Gets data by id which has ordering position;
  2. Second - Gets data by position-1;
  3. Third - Gets data by position+1.

I want to have only 1 Query which could take "needed one" by id + previous and next ones if they exists.

Queries:

First

set @position = 0;

SELECT 
`position` FROM
(
  SELECT `id`, @position:=@position+1 as `position` FROM {#table}          
  "other_part_of_query"
  ORDER BY `modified_time` DESC
) t
WHERE 
t.id = '".id."' 
LIMIT 1

Second and third

set @position = 0;

SELECT 
`id` FROM
(
  SELECT `id`, @position:=@position+1 as `position` FROM {#table}          
  "other_part_of_query"
  ORDER BY `modified_time` DESC
) t
WHERE 
t.`position` = '".position."' 
LIMIT 1

Upvotes: 2

Views: 98

Answers (3)

Vlad Alivanov
Vlad Alivanov

Reputation: 1234

Selecting next and previous rows of a specific row

SET @j = 0;
SET @i = 0;
SELECT *
FROM ( 
    SELECT id, col1, col2, ..., @j:=@j+1 AS pos
    FROM `table`
    WHERE  col1=... ORDER BY col1 DESC, col2 ASC
) AS zz
WHERE (    
    SELECT position
    FROM ( 
        SELECT id AS id2, @i:=@i+1 AS position
        FROM `table`
        WHERE  col1=... ORDER BY col1 DESC, col2 ASC
    ) AS zz
    WHERE id2=$currId
)
IN (pos-1, pos, pos+1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is complicated, because you are selecting a row by id, but choosing the adjacent ones by another field, modified_time.

The idea is to use variables to enumerate the rows. And, use another row to calculate the value for the id that you care about. Do this in a subquery, and then select the rows that you want:

SELECT t.*
FROM (SELECT `id`,
              @rn := if(@rnid := if(t.id  = '".id."', @rn + 1, @rnid),
                        @rn + 1, @rn + 1
                       ) as rn
      FROM {#table} t       
           "other_part_of_query" cross join
           (select @rn := 0, @rnid := 0) vars
      ORDER BY `modified_time` DESC
     ) t
WHERE rn in (@rnid - 1, @rnid, @rn)

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16691

You can use extra conditions in your where clause to solve this. Consider the following three conditions:

  • One row must have the modified_time you want.
  • One row must have the maximum modified_time that is still less than the one you want. (The previous position)
  • One row must have the minimum modified_time that is still greater than the one you want. (The next position)

Try this:

SELECT *
FROM myTable
WHERE modified_time = @myParam
   OR modified_time = (SELECT MAX(modified_time ) FROM myTable WHERE modified_time < @myParam)
   OR modified_time = (SELECT MIN(modified_time ) FROM myTable WHERE modified_time > @myParam);

Upvotes: 0

Related Questions