SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a query

    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!

  2. #2
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This seems to work, is there a better way?

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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or is there another way? Could this query be rewritten without subqueries?

  6. #6
    SitePoint Evangelist vikrantkorde's Avatar
    Join Date
    Jun 2004
    Location
    Mumbai, India
    Posts
    541
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Vikrant Korde
    S Y S T I M E, Mhape,
    Mumbai, Maharashtra, India.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •