How to select a row before a specific row on MySQL, if the table is ordered by date?

Question

I have a select result like this:

ID | DATE
----------------
10 | 2014-07-23
7  | 2014-07-24
8  | 2014-07-24
9  | 2014-07-24
1  | 2014-07-25
2  | 2014-07-25
6  | 2014-07-25
3  | 2014-07-26
4  | 2014-07-27
5  | 2014-07-28

The result above is ordered by date. Now, I want to select the one previous row before:

2  | 2014-07-25

Which is:

1  | 2014-07-25

In case I don't know the exact ID and the conditional code must be compatible with if I want to select a previous row of:

3  | 2014-07-26

Which is:

6  | 2014-07-25

What condition should I use?

UPDATE

Tried this:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, t1.*
  FROM table t1

Then I got this:

RANK | ID | DATE
----------------
1    | 10 | 2014-07-23
2    | 7  | 2014-07-24
3    | 8  | 2014-07-24
4    | 9  | 2014-07-24
5    | 1  | 2014-07-25
6    | 2  | 2014-07-25
7    | 6  | 2014-07-25
8    | 3  | 2014-07-26
9    | 4  | 2014-07-27
10   | 5  | 2014-07-28

Then I tried this:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, t1.*
  FROM table t1
  WHERE rank < 3;

I got this error: Unknown column 'rank' in 'where clause'.

What is the input? The `ID` of the row (ie 2 in the first case)? — Joachim Isaksson, Jul 25 '14 at 12:20
"*The table above is ordered by date*" - no it's not. Rows in a relational database are not sorted. Your _result_ is ordered by date, not the "table" — , Jul 25 '14 at 13:35

score 2 · Accepted Answer · answered Jul 25 '14 at 12:30

Here's one way...

 DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID INT NOT NULL PRIMARY KEY
,DATE DATE NOT NULL
);

INSERT INTO my_table VALUES
(10 ,'2014-07-23'),
(7   ,'2014-07-24'),
(8   ,'2014-07-24'),
(9   ,'2014-07-24'),
(1   ,'2014-07-25'),
(2   ,'2014-07-25'),
(6   ,'2014-07-25'),
(3   ,'2014-07-26'),
(4   ,'2014-07-27'),
(5   ,'2014-07-28');

SELECT a.id
     , a.date
     , b.id b_id
     , b.date b_date
  FROM 
     ( SELECT x.* 
            , COUNT(*) rank 
         FROM my_table x 
         JOIN my_table y 
           ON (y.date < x.date) 
           OR (y.date = x.date AND y.id <= x.id) 
        GROUP 
           BY x.date
         , x.id
     ) a
  LEFT
  JOIN
     ( SELECT x.* 
            , COUNT(*) rank 
         FROM my_table x 
         JOIN my_table y 
           ON (y.date < x.date) 
           OR (y.date = x.date AND y.id <= x.id) 
        GROUP 
           BY x.date
         , x.id
     ) b
    ON b.rank = a.rank - 1;


+----+------------+------+------------+
| id | date       | b_id | b_date     |
+----+------------+------+------------+
| 10 | 2014-07-23 | NULL | NULL       |
|  7 | 2014-07-24 |   10 | 2014-07-23 |
|  8 | 2014-07-24 |    7 | 2014-07-24 |
|  9 | 2014-07-24 |    8 | 2014-07-24 |
|  1 | 2014-07-25 |    9 | 2014-07-24 |
|  2 | 2014-07-25 |    1 | 2014-07-25 |
|  6 | 2014-07-25 |    2 | 2014-07-25 |
|  3 | 2014-07-26 |    6 | 2014-07-25 |
|  4 | 2014-07-27 |    3 | 2014-07-26 |
|  5 | 2014-07-28 |    4 | 2014-07-27 |
+----+------------+------+------------+

... but you can also do this (quicker) with variables.

score 0 · Answer 2 · answered Jul 25 '14 at 12:32

0

You can add a row id to the select like this

SELECT @rowid:=@rowid+1 as rowid,
t1.* FROM yourdatabase.tablename t1, (SELECT @rowid:=0) as rowids;

Then you can run a simple query to get the lower rowid from the input.

answered Jul 25 '14 at 12:32

Mad Dog Tannen

7,129
5
31
55

I tried this, but I don't really understand the next step. How to select the previous rowid that I want. I tried to add where clause where rowid < 3, but it didn't work. Unknown column 'rowid' in 'where clause' – Kelvin Latif Jul 25 '14 at 12:54

score 0 · Answer 3 · answered Jul 25 '14 at 13:27

This uses a sub query that joins the table against itself, where on one side it is the date you are checking and matching against smaller dates. It uses MAX to get the highest smaller date.

This is then joined against another sub query that gets the highest ID for each date, which also joins against the table itself to get the other details from that row.

SELECT table.*
FROM table
INNER JOIN
(
    SELECT MAX(a.date) AS latest_prev_date
    FROM table1 a
    INNER JOIN table1 b
    ON a.date > b.date
    WHERE a.date = '2014-07-26'
) sub0
ON table.date = sub0.latest_prev_date
INNER JOIN
(
    SELECT date, MAX(ID) AS latest_prev_id
    FROM table1 
    GROUP BY date
) sub1
ON table.ID = sub1.latest_prev_id
AND sub1.date = sub0.latest_prev_date

John Ruddell · Answer 4 · 2014-07-25T13:38:49.993

if you want to use a user_defined_variable this is a way to do it.

SELECT 
    tab.id, temp.id, temp.date
FROM
(
    SELECT 
        @A:=@A + 1 AS rank_col, t.date, t.id
    FROM
        myTable t
    CROSS JOIN (SELECT @A:=0) join_table
) AS tab
LEFT JOIN
(
    SELECT 
        @B:=@B + 1 AS rank_col, t2 . *
    FROM myTable t2
    CROSS JOIN (SELECT @B:=0) join_table1
) temp ON temp.rank_col = tab.rank_col - 1;

DEMO

How to select a row before a specific row on MySQL, if the table is ordered by date?

4 Answers4