Help with a query

[dekker]

Hi,

I have a table with a DATE as the primary key, so there's one row per date. I need a query that returns exactly three rows: The one indexed by the date I select (WHERE date='20050302'), plus the two neighbouring dates; the previous and following entries relative to the date I selected.

My table would look like this:

20050301 | abc
20050303 | def
20050308 | ghi
20050310 | jkl
20050315 | mno
20050320 | pqr

I run a SELECT query with "WHERE date='20050308'" and I get 'ghi'. Now what I want in addition to that are the two neighbouring rows, in this case 'def' and 'jkl'. I do not know the dates of those entries, so I can't explicitly include them in the WHERE clause.

The query i'm looking for will obviously contain "ORDER BY date", but how do I get the previous and following row relative to the one I select?

Thanks in advance!

[dekker]

This seems to work, is there a better way?

SELECT * FROM f_photos
WHERE 
datum='2005-07-21' OR
datum>'2005-07-21' OR 
datum<'2005-07-21' 
LIMIT 3

[r937]

yes, there is

when you look at the logic of your WHERE clause, the three conditions basically cover all possible dates

then, without an ORDER BY, you are going to get back any 3 rows at all

this is what you want --

select thedate
     , ( select min(thedate)
           from yourtable
          where thedate > A.thedate ) as next 
     , ( select max(thedate)
           from yourtable
          where thedate < A.thedate ) as prev
  from yourtable as A
 where thedate = '2005-03-03'

[dekker]

when you look at the logic of your WHERE clause, the three conditions basically cover all possible dates

Ouch, of course. Sorry for being so stupid.

I learned a lot from analyzing your query, thanks! Now all I have to do is get my provider to update their mySQL installation.. They're still running 4.0.1. Failing that, I'll probably have to split the subqueries into separate queries.

[dekker]

Or is there another way? Could this query be rewritten without subqueries?

[vikrantkorde]

hi dekker,

without sub query it is possible but then u need to file 3 queries. which is like the separating the where conditions written in the first solution. I doubt if the first query is always give the expected result.

if you are using the older versions of mysql then go for the first solution with the separated logic. else go for the 2nd solution.

for the separatoion you need these 3 queries.
SELECT * FROM f_photos WHERE datum='2005-07-21' LIMIT 1
SELECT * FROM f_photos WHERE datum>'2005-07-21' order by datum DESC LIMIT 1
SELECT * FROM f_photos WHERE datum<'2005-07-21' order by datum ASC LIMIT 1