SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    basic select q: selecting one row per unique value of the most recent

    hello,

    i've got a table with a datetime column and a date column. they'll be mulitple rows of data which have the same date (along with multiple rows of data with other dates). i want to select each date (each unique one) but only one row per date -- the one with the latest datetime.

    SELECT datetime,date FROM table ...?

    i suspect GROUP BY might be useful?, but am not sure how to do it. how to do it? thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    any chance you can explain why there are two columns instead of just one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    they're totally different data. datetime is the datetime the info was published, the date is the date the info in the row is about.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    and are the column names really `datetime` and `date` ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    the datetime column is called pub_datetime and the date column is called date.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t.pub_datetime 
         , t.`date`
      FROM ( SELECT `date`
                  , MAX(pub_datetime) AS last_pub
               FROM daTable
             GROUP
                 BY `date` ) AS m
    INNER
      JOIN daTable AS t
        ON t.`date` = m.`date`
       AND t.pub_datetime = m.last_pub
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    blimey this is a bit more complicated than i thought, no wonder i had trouble with it. thanks very much for the answer. it's not quite working though -- nearly is though. for most (but not all) pub_datetime's only one date is given. but i realise the info i provided wasn't enough, so, all info:

    the table:
    Code:
    CREATE TABLE IF NOT EXISTS daTable (
      pub_datetime datetime NOT NULL,
      `date` date NOT NULL,
      score tinyint,
      PRIMARY KEY  (pub_datetime,`date`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    data:
    Code:
    INSERT INTO `daTable` (`pub_datetime`, `date`, `score`) VALUES
    ('2010-05-18 19:50:00', '2010-05-19', 27),
    ('2010-05-18 19:50:00', '2010-05-20', 26),
    ('2010-05-18 19:50:00', '2010-05-21', 24),
    ('2010-05-19 01:31:00', '2010-05-19', 27),
    ('2010-05-19 01:31:00', '2010-05-20', 24),
    ('2010-05-19 01:31:00', '2010-05-21', 28),
    ('2010-05-19 07:35:00', '2010-05-19', 27),
    ('2010-05-19 07:35:00', '2010-05-20', 25),
    ('2010-05-19 07:35:00', '2010-05-21', 28),
    ('2010-05-19 19:50:00', '2010-05-20', 25),
    ('2010-05-19 19:50:00', '2010-05-21', 28),
    ('2010-05-19 19:50:00', '2010-05-22', 24),
    ('2010-05-20 01:31:00', '2010-05-20', 26),
    ('2010-05-20 01:31:00', '2010-05-21', 26),
    ('2010-05-20 01:31:00', '2010-05-22', 28),
    ('2010-05-20 07:35:00', '2010-05-20', 26),
    ('2010-05-20 07:35:00', '2010-05-21', 26),
    ('2010-05-20 07:35:00', '2010-05-22', 28),
    ('2010-05-20 19:45:00', '2010-05-21', 26),
    ('2010-05-20 19:45:00', '2010-05-22', 28),
    ('2010-05-20 19:45:00', '2010-05-23', 28),
    ('2010-05-21 01:31:00', '2010-05-21', 26),
    ('2010-05-21 01:31:00', '2010-05-22', 24),
    ('2010-05-21 01:31:00', '2010-05-23', 28),
    ('2010-05-21 07:35:00', '2010-05-23', 28),
    ('2010-05-21 07:35:00', '2010-05-22', 24),
    ('2010-05-21 07:35:00', '2010-05-21', 26),
    ('2010-05-21 10:46:00', '2010-05-21', 23),
    ('2010-05-21 10:46:00', '2010-05-22', 26),
    ('2010-05-21 10:46:00', '2010-05-23', 29),
    ('2010-05-21 19:50:00', '2010-05-22', 26),
    ('2010-05-21 19:50:00', '2010-05-23', 29),
    ('2010-05-21 19:50:00', '2010-05-24', 26),
    ('2010-05-22 01:31:00', '2010-05-22', 28),
    ('2010-05-22 01:31:00', '2010-05-23', 31),
    ('2010-05-22 01:31:00', '2010-05-24', 24),
    ('2010-05-22 07:35:00', '2010-05-22', 28),
    ('2010-05-22 07:35:00', '2010-05-23', 31),
    ('2010-05-22 07:35:00', '2010-05-24', 24),
    ('2010-05-22 19:55:00', '2010-05-23', 31),
    ('2010-05-22 19:55:00', '2010-05-24', 24),
    ('2010-05-22 19:55:00', '2010-05-25', 23),
    ('2010-05-23 01:31:00', '2010-05-23', 27),
    ('2010-05-23 01:31:00', '2010-05-24', 24),
    ('2010-05-23 01:31:00', '2010-05-25', 19),
    ('2010-05-23 07:35:00', '2010-05-23', 27),
    ('2010-05-23 07:35:00', '2010-05-24', 24),
    ('2010-05-23 07:35:00', '2010-05-25', 19),
    ('2010-05-23 16:50:00', '2010-05-23', 27),
    ('2010-05-23 16:50:00', '2010-05-24', 24),
    ('2010-05-23 16:50:00', '2010-05-25', 19),
    ('2010-05-23 19:50:00', '2010-05-24', 24),
    ('2010-05-23 19:50:00', '2010-05-25', 19),
    ('2010-05-23 19:50:00', '2010-05-26', 17),
    ('2010-05-24 01:30:00', '2010-05-24', 22),
    ('2010-05-24 01:30:00', '2010-05-25', 10),
    ('2010-05-24 01:30:00', '2010-05-26', 17),
    ('2010-05-24 07:35:00', '2010-05-24', 22),
    ('2010-05-24 07:35:00', '2010-05-25', 10),
    ('2010-05-24 07:35:00', '2010-05-26', 17),
    ('2010-05-24 19:50:00', '2010-05-25', 10),
    ('2010-05-24 19:50:00', '2010-05-26', 17),
    ('2010-05-24 19:50:00', '2010-05-27', 19),
    ('2010-05-25 01:31:00', '2010-05-25', 10),
    ('2010-05-25 01:31:00', '2010-05-26', 19),
    ('2010-05-25 01:31:00', '2010-05-27', 20),
    ('2010-05-25 07:35:00', '2010-05-25', 10),
    ('2010-05-25 07:35:00', '2010-05-26', 19),
    ('2010-05-25 07:35:00', '2010-05-27', 20),
    ('2010-05-25 14:50:00', '2010-05-25', 17),
    ('2010-05-25 14:50:00', '2010-05-26', 19),
    ('2010-05-25 14:50:00', '2010-05-27', 20),
    ('2010-05-25 19:50:00', '2010-05-26', 19),
    ('2010-05-25 19:50:00', '2010-05-27', 20),
    ('2010-05-25 19:50:00', '2010-05-28', 18),
    ('2010-05-26 01:31:00', '2010-05-26', 19),
    ('2010-05-26 01:31:00', '2010-05-27', 17),
    ('2010-05-26 01:31:00', '2010-05-28', 16),
    ('2010-05-26 07:35:00', '2010-05-26', 19),
    ('2010-05-26 07:35:00', '2010-05-27', 15),
    ('2010-05-26 07:35:00', '2010-05-28', 16),
    ('2010-05-26 19:45:00', '2010-05-27', 16),
    ('2010-05-26 19:45:00', '2010-05-28', 16),
    ('2010-05-26 19:45:00', '2010-05-29', 11),
    ('2010-05-27 01:31:00', '2010-05-27', 22),
    ('2010-05-27 01:31:00', '2010-05-28', 21),
    ('2010-05-27 01:31:00', '2010-05-29', 16),
    ('2010-05-27 07:35:00', '2010-05-27', 23),
    ('2010-05-27 07:35:00', '2010-05-28', 21),
    ('2010-05-27 07:35:00', '2010-05-29', 14),
    ('2010-05-27 19:40:00', '2010-05-28', 21),
    ('2010-05-27 19:40:00', '2010-05-29', 14),
    ('2010-05-27 19:40:00', '2010-05-30', 17),
    ('2010-05-28 01:31:00', '2010-05-28', 18),
    ('2010-05-28 01:31:00', '2010-05-29', 14),
    ('2010-05-28 01:31:00', '2010-05-30', 18),
    ('2010-05-28 05:50:00', '2010-05-28', 18),
    ('2010-05-28 05:50:00', '2010-05-29', 14),
    ('2010-05-28 05:50:00', '2010-05-30', 18),
    ('2010-05-28 07:35:00', '2010-05-28', 18),
    ('2010-05-28 07:35:00', '2010-05-29', 15),
    ('2010-05-28 07:35:00', '2010-05-30', 18),
    ('2010-05-28 19:40:00', '2010-05-29', 16),
    ('2010-05-28 19:40:00', '2010-05-30', 18),
    ('2010-05-28 19:40:00', '2010-05-31', 16),
    ('2010-05-29 01:31:00', '2010-05-29', 12),
    ('2010-05-29 01:31:00', '2010-05-30', 19),
    ('2010-05-29 01:31:00', '2010-05-31', 19),
    ('2010-05-29 07:35:00', '2010-05-29', 11),
    ('2010-05-29 07:35:00', '2010-05-30', 19),
    ('2010-05-29 07:35:00', '2010-05-31', 19),
    ('2010-05-29 19:45:00', '2010-05-30', 19),
    ('2010-05-29 19:45:00', '2010-05-31', 18),
    ('2010-05-29 19:45:00', '2010-06-01', 18),
    ('2010-05-30 01:31:00', '2010-05-30', 18),
    ('2010-05-30 01:31:00', '2010-05-31', 15),
    ('2010-05-30 01:31:00', '2010-06-01', 15),
    ('2010-05-30 07:35:00', '2010-05-30', 18),
    ('2010-05-30 07:35:00', '2010-05-31', 15),
    ('2010-05-30 07:35:00', '2010-06-01', 18),
    ('2010-05-30 14:50:00', '2010-05-30', 18),
    ('2010-05-30 14:50:00', '2010-05-31', 15),
    ('2010-05-30 14:50:00', '2010-06-01', 18),
    ('2010-05-30 19:40:00', '2010-05-31', 15),
    ('2010-05-30 19:40:00', '2010-06-01', 16),
    ('2010-05-30 19:40:00', '2010-06-02', 25),
    ('2010-05-31 02:21:00', '2010-05-31', 15),
    ('2010-05-31 02:21:00', '2010-06-01', 20),
    ('2010-05-31 02:21:00', '2010-06-02', 23),
    ('2010-05-31 03:20:00', '2010-05-31', 15),
    ('2010-05-31 03:20:00', '2010-06-01', 20),
    ('2010-05-31 03:20:00', '2010-06-02', 23),
    ('2010-05-31 05:51:00', '2010-05-31', 15),
    ('2010-05-31 05:51:00', '2010-06-01', 19),
    ('2010-05-31 05:51:00', '2010-06-02', 23),
    ('2010-05-31 07:35:00', '2010-05-31', 15),
    ('2010-05-31 07:35:00', '2010-06-01', 18),
    ('2010-05-31 07:35:00', '2010-06-02', 23);
    the sql query results in (printed out using php, including making the dates relative):

    Code:
    pub_datetime            date            score
    ----------------------------------------------
    Wed 19th May 7.35am     Wed 19th May    28
    Thu 20th May 7.35am     Thu 20th May    27
    Fri 21st May 10.46am    Fri 21st May    24
    Sat 22nd May 7.35am     Sat 22nd May    29
    Sun 23rd May 4.50pm     Sun 23rd May    28
    Mon 24th May 7.35am     Mon 24th May    23
    Tuesday 2.50pm          Tuesday         18
    Wednesday 7.35am        Wednesday       20
    Thursday 7.35am         Thursday        24
    Friday 7.35am           Friday          19
    Saturday 7.35am         Saturday        12
    Yesterday 2.50pm        Yesterday       19
    Today 7.35am            Today           16
                            Tomorrow        19
                            Wednesday       24
    
    edit: just to make clear, those last three rows all
    have the pub_datetime of Today 7.35am
    the query i used, pretty much identical to what you supplied:
    Code:
    SELECT t.pub_datetime 
         , t.`date`
         , t.score
      FROM ( SELECT `date`
                  , MAX(pub_datetime) AS last_pub
               FROM daTable
             GROUP
                 BY `date` ) AS m
    INNER
      JOIN daTable AS t
        ON t.`date` = m.`date`
       AND t.pub_datetime = m.last_pub
    so the problem is (due to me not giving all info i'm sure, sorry) there's only one date per pub_datetime, apart from the last one, the 'Today 7.35am' one. how the 'Today 7.35am' one is working is how all the others should work. how to do that? thanks v. much

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, what you posted doesn't make any sense at all

    you originally said "i want to select each date (each unique one) but only one row per date -- the one with the latest datetime."

    now it sounds like you want to select each datetime (each unique one) but only one row per datetime -- the one with the latest date

    can you see why i am confused
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    same results again, but without the dates relative, incase that's useful:
    Code:
    pub_datetime            date            score
    ----------------------------------------------
    2010-05-19 07:35:00     2010-05-19      28
    2010-05-20 07:35:00     2010-05-20      27
    2010-05-21 10:46:00     2010-05-21      24
    2010-05-22 07:35:00     2010-05-22      29
    2010-05-23 16:50:00     2010-05-23      28
    2010-05-24 07:35:00     2010-05-24      23
    2010-05-25 14:50:00     2010-05-25      18
    2010-05-26 07:35:00     2010-05-26      20
    2010-05-27 07:35:00     2010-05-27      24
    2010-05-28 07:35:00     2010-05-28      19
    2010-05-29 07:35:00     2010-05-29      12
    2010-05-30 14:50:00     2010-05-30      19
    2010-05-31 07:35:00     2010-05-31      16
                            2010-06-01      19
                            2010-06-02      24

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    see post #8 and let me know which way you want it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, what you posted doesn't make any sense at all

    you originally said "i want to select each date (each unique one) but only one row per date -- the one with the latest datetime."

    now it sounds like you want to select each datetime (each unique one) but only one row per datetime -- the one with the latest date

    can you see why i am confused
    yes i can, sorry.

    i'm after each and every most recent, recent according to pub_datetime that is, `date`.

    does that clarify?

  12. #12
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    see post #8 and let me know which way you want it
    yup, sorry, i posted #9 before i saw #8. thanks.

  13. #13
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    the freshest `date` for each and every `date` where "freshest" is according to pub_datetime.

    i think that says it, i think (possibly, but obviously only you/others can judge) that's clear.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnyboy View Post
    the freshest `date` for each and every `date` where "freshest" is according to pub_datetime.
    sorry, that makes it worse

    do you want the latest date for each pub_datetime

    or the latest pub_datetime for each date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, that makes it worse
    oh no.

    do you want the latest date for each pub_datetime
    no,

    or the latest pub_datetime for each date
    yup, that's it

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnyboy View Post
    yup, that's it
    finally!

    the answer to this was already given in post #6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,301
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    oh my god, yup, you're right. i've been messing with this data quite a bit and got so used to there being three days (`date`s) per publish date (which is how the data always comes) that when there weren't three per publish date i automatically/lazily assumed something was wrong, but of course there wasn't as you pointed out.

    great, thanks very much r937


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
  •