开发者

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 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜