MySQL statement that groups results, but also returns the lowest and the highest value of another column from within the group
Ok, so this is my hypothetical table:
Id Page Timestamp Etc
1 1 2009-10-10 ...
2 1 2009-10-13 ...
3 1 2009-10-14 ...
4 1 2009-10-20 ...
5 2 2009-10-24 ...
6 2 2009-10-27 ...
7 2 2009-11-06 ...
8 3 2009-11-06 ...
9 4 2009-11-07 ...
10 4 2开发者_高级运维009-11-20 ...
11 4 2009-11-21 ...
And what I need is a query that in each row returns the page number...
SELECT * FROM `hypothetical_table` WHERE `Visible` = 1 AND `foo` = 'blargh' GROUP BY `Page` HAVING COUNT(`Page`) >= 1 ORDER BY `Page` ASC
... but also returns the lowest and the highest value from the Timestamp column within the group. So that on the flipside I can output something like:
• Page 1 (from October 10th 2009 to October 20th 2009)
• Page 2 (from October 24th 2009 to November 6th 2009) • Page 3 (November 6th 2009) • Page 4 (from November 7th 2009 to November 21th 2009)Is this even doable with straight MySQL?
How about:
SELECT `Page`, MIN(`Timestamp`) as StartDate, MAX(`Timestamp`) as EndDate
FROM `hypothetical_table`
WHERE `Visible` = 1
AND `foo` = 'blargh'
GROUP BY `Page`
HAVING COUNT(`Page`) >= 1
ORDER BY `Page` ASC
精彩评论