-1

Is is possible to construct a SELECT statement that contains a WHERE clause that uses the value from a column in the "next" row?

I have a date and time field date_entered. ie. given a table with a field named "date_entered" with the following values I want a SELECT statement that selects the rows "WHERE date_entered>='current_date' and date_entered<=[NEXT ROW]date_entered "

I tried Post but not found what I am looking at.

Community
  • 1
  • 1
Naeem
  • 36
  • 9

1 Answers1

1

If your records are ordered chronologically, then you may get through with a query like this:

SELECT 
    t1.*,
    min(t2.date_entered) as next_row_date 
from mytable t1 
join mytable t2
/* ON nothing */
where t1.id < t2.id
and t1.date_entered > NOW()
group by t1.id
having t1.date_entered <= next_row_date

If you really need next row date... You will likely get a better result with a subquery. Something like

SELECT 
    t1.*,
from mytable t1 
where t1.date_entered > NOW()
and t1.date_entered < ( SELECT date_entered from mytable t2 where t2.id > t1.id limit 1)
mika
  • 1,971
  • 3
  • 18
  • 32
  • Thanks for your response but my records are not ordered chronologically. – Naeem Apr 09 '14 at 10:58
  • t.id and t2.id are not auto increment value rather they are 36 key values. – Naeem Apr 09 '14 at 11:04
  • So how do you know which record is "next" ? If you have a record with key A, what would be your query to find the following record ? This is what the subselect is doing. – mika Apr 09 '14 at 11:12
  • Thanks @mika its helps me to some extent – Naeem Apr 10 '14 at 10:00
  • @Naeem you are welcome. I am glad I could help. How did you solve your issue in the end ? Do you mind posting your solution ? – mika Apr 11 '14 at 10:59