Max record by group

[Duane Boudreau]

I have a table that stores a record of user access in a usage table. The
data looks something like:

UserID SectionID PageID VisitDate
1 1 1 1/25/08
1 1 1 1/22/08
1 1 2 1/25/08
2 1 1 1/25/08
2 1 1 1/22/08

Since each user can have multiple records in the table how can I get a list
of users and their last visit date like so:

UserID SectionID PageID VisitDate
1 1 1 1/25/08
1 1 2 1/25/08
2 1 1 1/25/08

I'm drawing a complete blank. I tried using a group by and max functions but
haven't had any success yet.

Thanks,
Duane



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3025
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Bruce, Rodney Mr CTR USA AMC]

Select Userid, SectionID, Pageid, max(VisitDate) as MaxDate
From UsageTable
Group by UserID, SectionID, PageID
Order by UserID, SectionID, PageID

That query should return a max date for each combination of UserID,
SectionID, PageID.
(at least in MS SQL)

Is that what your looking for?

-----Original Message-----
From: Duane [mailto:duane@...]
Sent: Friday, January 25, 2008 3:54 PM
To: SQL
Subject: Max record by group

I have a table that stores a record of user access in a usage table. The
data looks something like:

UserID SectionID PageID VisitDate
1 1 1 1/25/08
1 1 1 1/22/08
1 1 2 1/25/08
2 1 1 1/25/08
2 1 1 1/22/08

Since each user can have multiple records in the table how can I get a
list of users and their last visit date like so:

UserID SectionID PageID VisitDate
1 1 1 1/25/08
1 1 2 1/25/08
2 1 1 1/25/08

I'm drawing a complete blank. I tried using a group by and max functions
but haven't had any success yet.

Thanks,
Duane





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3026
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Jochem van Dieten-2]

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Jochem


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3027
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6