CODEWITHSUNDEEP
mysqlselect
marvin
marvin

Reputation: 375

How to select only last rows that satisfies conditions?

I'm trying to select rows where x=5, but x changes constantly. So I have such a table:

 id    x
----  ---
 1     5
 2     6
 3     4
 4     5
 5     5

So I want to perform a query like "SELECT * FROM table WHERE x=5 AND _???_;" so that it returns rows 4 & 5 but not row 1.

In other words, I want to get the rows where x had this value most recently. I hope I made myself clear. Thanks!

edit: Number of entries after x got the last value my change. I mean the table could also be like this:

 id    x
----  ---
 1     5
 2     6
 3     4
 4     5
 5     1
 6     5
 7     5
...    5
100    5
101    5

in this case it should return rows [6-101].

Upvotes: 2

Views: 5353

Answers (2)

valex
valex

Reputation: 24144

SQLFiddle demo

select * from t t1
where 
x=(select x from t order by id desc limit 1)
and
not exists
(select x from t where id>t1.id and x<>t1.x)

or SQLFiddle demo

select * from t t1
where 
x=(select x from t order by id desc limit 1)
and
id>=
(select max(id) from t 
  where x<>
   (select x from t order by id desc limit 1)
)

Select what is faster on your base.

Upvotes: 1

Elby
Elby

Reputation: 1674

Following wil get recent row 

SELECT * FROM table WHERE x=5 ORDER BY id DESC LIMIT 0,1

Upvotes: 2

Related Questions