SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast lrempel's Avatar
    Join Date
    Jan 2005
    Location
    Vancouver
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    complex sql query

    hello,

    i am not sure if what i want to do is possible, but hopefully it is, and someone out there can tell me how to do it! i will simplify the problem to just deal with the part i can't figure out.

    anyways, here's a sample db setup: (dots are just for formatting - i have no idea how to make a table in this forum)

    tbl_info:

    name . . . location . . . . . date

    bob . . . . . usa . . . . . 2005-02-16
    bob . . . . . usa . . . . . 2005-02-18
    bob . . . . . usa . . . . . 2005-02-20
    bob . . . . . usa . . . . . 2005-02-22
    sam . . . . canada . . . 2005-02-18
    sam . . . . canada . . . 2005-02-21
    sam . . . . canada . . . 2005-02-24

    so, what i want to do is select between a certain date range, say 2005-02-16 to 2005-02-24. the tricky part is, i only want to actually select the results with the oldest and most recent date (in the range) where all other fields are the same.

    ie: the results would be:

    bob . . . usa . . . 2005-02-16
    bob . . . usa . . . 2005-02-22
    sam . . canada . 2005-02-18
    sam . . canada . 2005-02-24

    anyone know if this is possible? any help on this would be appreciated.

    thanks.

  2. #2
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure about how to produce the format you want with min and max dates as separate rows, but you could use a GROUP BY clause in your query to get the min and max date for each name:

    SELECT name, location, MIN(date), MAX(date)
    FROM tbl_info
    WHERE date BETWEEN '2005-02-16' AND '2005-02-24'
    GROUP BY name;

    would give this:

    +------+----------+------------+------------+
    | name | location | MIN(date) | MAX(date) |
    +------+----------+------------+------------+
    | bob | usa | 2005-02-16 | 2005-02-22 |
    | sam | canada | 2005-02-18 | 2005-02-24 |
    +------+----------+------------+------------+

    Hope this helps.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I misread your criteria - if the location is also significant then you should change the GROUP BY clause to include it:

    ...GROUP BY name, location;

  4. #4
    SitePoint Enthusiast lrempel's Avatar
    Join Date
    Jan 2005
    Location
    Vancouver
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that works well enough for my purposes. thanks a lot!

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look up in the manual using GROUP WITH HIDDEN FIELDS they warn you that you could get unpredictable results. Any other database application would give you an error for leaving out a field in your group by that was in your select. (Except for aggregate fields).


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
  •