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.
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 
sorry, that makes it worse
do you want the latest date for each pub_datetime
or the latest pub_datetime for each date
yes i can, sorry.
i’m after each and every most recent, recent according to pub_datetime that is, date.
does that clarify?
yup, sorry, i posted #9 before i saw #8. thanks.
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.
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 (dates) 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
see post #8 and let me know which way you want it
any chance you can explain why there are two columns instead of just one?
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
same results again, but without the dates relative, incase that’s useful:
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
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:
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:
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):
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:
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
finally!
the answer to this was already given in post #6
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
the datetime column is called pub_datetime and the date column is called date.
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.
and are the column names really datetime and date ??