CODEWITHSUNDEEP
mysql
Goku
Goku

Reputation: 1840

Select last records before find some field value

How can I get the last(talking about datetime) records from a table before find some field value?
If I have this table:

+----+---------------------+-------+-----------+
| id | date                |  flag | data      |
+----+---------------------+-------------------+
|  1 | 2008-09-01 15:02:00 |   1   | ....    . |
|  2 | 2008-09-01 16:15:00 |   5   | ....    . |
|  3 | 2008-09-01 16:19:00 |   4   | ....    . |
|  4 | 2008-09-01 16:20:00 |   4   | ....    . |
|  5 | 2008-09-01 16:20:00 |   5   | ....    . |
|  6 | 2008-09-01 18:18:00 |   9   | ....    . |
+----+---------------------+-------+-----------+

I want to get the last records before find "4" flag field value, I mean:

SELECT * FROM mytable WHERE..... ORDER BY id DESC

+----+---------------------+-------+-----------+
| id | date                |  flag | data      |
+----+---------------------+-------------------+
|  5 | 2008-09-01 18:18:00 |   9   | ....    . |
|  4 | 2008-09-01 16:20:00 |   5   | ....    . |
+----+---------------------+-------+-----------+

How can I get it?

Added: Additionally I would know how get the records with the first occurrence of "4" included
Added: The flag field doesn't have ordered values
Thanks

Upvotes: 1

Views: 169

Answers (2)

Alma Do
Alma Do

Reputation: 37365

So since it is about descending order, just do:

SELECT * FROM mytable WHERE flag>4 ORDER BY id DESC

About your 'addition' - there's no 'first' record in terms of SQL since they all are equal when we're speaking of order. I.e. you should first specify order inside same flag values, then you'll be able to determine which row is first.

Based on comments

If we're speaking about ordering with date field, that will be:

1) Get all records with date field greater than maximum date for 4-flag row:

SELECT 
  * 
FROM 
  mytable 
WHERE 
  `date`>(SELECT MAX(`date`) FROM mytable WHERE flag=4) 
  AND 
  flag!=4

2) Get all records with date field greater than maximum date for 4-flag row and include this row:

SELECT 
  * 
FROM 
  mytable 
WHERE 
  `date`>(SELECT MAX(`date`) FROM mytable WHERE flag=4)
  AND
    flag!=4
UNION ALL
SELECT 
  * 
FROM 
  mytable 
WHERE 
  `date`=(SELECT MAX(`date`) FROM mytable WHERE flag=4) 
  AND 
  flag=4

-note, in second case we can't do just SELECT * FROM mytable WHERE date>=(SELECT MAX(date) FROM mytable WHERE flag=4) since there could be rows with date field that are same as maximum value for date for 4-flag rows, but with another flag.

Upvotes: 2

fthiella
fthiella

Reputation: 49049

If your ID are always ordered, you could use something like this:

SELECT *
FROM mytable
WHERE id>(SELECT MAX(id) FROM mytable WHERE flag="4")

or you could use the datetime field:

SELECT *
FROM mytable
WHERE
  date>=(SELECT MAX(date) FROM mytable WHERE flag="4")
  AND flag!="4"

and the records with the firs occurrence of "4":

SELECT *
FROM mytable
WHERE date=(SELECT MIN(date) FROM mytable WHERE flag="4")

Upvotes: 2

Related Questions