Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query of last records(timestamp) for Distinct CategoryId's in same table. Expand / Collapse
Author
Message
Posted Monday, October 11, 2010 12:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:45 PM
Points: 3, Visits: 26
I have a table of Activities.

Table Activities:
Id Int Identity
CategoryId Int
Activity nvarchar(255)
StartDateTime datetime

This activities table has a CategoryId in each row as well as a StartDateTime column for each activity.
My task is to come up with a query that produces the last activity for each CategoryId in the table.
Currently there are 731 distinct CategoryId's in the table with a total of 71,000 Activity records.

Any help formulating a query that gives me the last activity(by StartDateTime) for each CategoryId in the table would greatly be apreciated.
Post #1002381
Posted Monday, October 11, 2010 12:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 998, Visits: 3,129
How about:

SELECT MAX(Datefield) as MostRecentDate, CategoryID
FROM Table
GROUP BY CategoryID

Or, did I miss something?


Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1002393
Posted Monday, October 11, 2010 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:45 PM
Points: 3, Visits: 26
That did it.
Wasn't even thinking about MAX()!!!!

That was the missing link.
I knew there was a simple solution.


Thanks a bunch.
Post #1002396
Posted Monday, October 11, 2010 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:45 PM
Points: 3, Visits: 26
This is what i used:
SELECT
MAX(ACTIVITIES.StartDateTime) as LastStartDateTime,
ACTIVITIES.CategoryId
FROM Activities
GROUP BY ACTIVITIES.CategoryId

This query gave me the 731 results i wanted, however i also need the Activity.Id

I tried this but was unsuccessful:

SELECT
MAX(ACTIVITIES.StartDateTime) as LastStartDateTime,
ACTIVITIES.CategoryId,
ACTIVITIES.Id
FROM Activities
GROUP BY ACTIVITIES.CategoryId, ACTIVITIES.Id

This query results in 69378 results.

Am I still missing something here???
Post #1002401
Posted Monday, October 11, 2010 7:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:22 PM
Points: 188, Visits: 76
could you:
SELECT top(1) [ACTIVITIES.StartDateTime]
,ACTIVITIES.CategoryId
,ACTIVITIES.Id

FROM [Activities]

order by [ACTIVITIES.StartDateTime] desc

?
Post #1002520
Posted Tuesday, October 12, 2010 6:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 998, Visits: 3,129
Think about what you have, and what you need.

By grouping both fields, you have all of the unique combinations of the category and activity for the date.

You table probably contains data like this:
01/01/2000 1 1
01/02/2000 1 2
01/03/2000 1 3

If this is true, you want one row returned, containing:
01/03/2000 1 3

By grouping on both fields, you would get all three rows in the above example, not one, which is what you want. There are numerious ways to do this, but joining on a subquery should provide you with the results you need.
How about:
SELECT
A.StartDateTime,
A.CategoryId,
A.Id
FROM Activities as A
INNER JOIN
(SELECT
MAX(ACTIVITIES.StartDateTime) as LastStartDateTime,
ACTIVITIES.CategoryId
FROM Activities
GROUP BY ACTIVITIES.CategoryId) as B ON A.StartDateTime = B.LastStartDateTime
A.CategoryID = B.CategoryID



Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1002724
Posted Friday, October 15, 2010 7:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 11:44 AM
Points: 69, Visits: 136
Here's another way using row_number/partitioning

DECLARE @tDUMMY TABLE (
StartDate DATE,
Activity VARCHAR(100),
CategoryID INT
);

INSERT INTO @tDUMMY

SELECT CONVERT(DATE, '1876-01-01'), 'Action in 1876', 1 UNION ALL
SELECT CONVERT(DATE, '1945-01-01'), 'Action in 1945', 1 UNION ALL
SELECT CONVERT(DATE, '2000-01-01'), 'Chaos', 2 UNION ALL
SELECT CONVERT(DATE, '2010-10-15'), 'Stare at wall', 3 UNION ALL
SELECT CONVERT(DATE, '2010-10-16'), 'Unknown', 3 UNION ALL
SELECT CONVERT(DATE, '2012-01-01'), '????', 4 UNION ALL
SELECT CONVERT(DATE, '4712-01-01'), 'Faraway', 4

;


SELECT StartDate, Activity, CategoryID FROM
(
SELECT StartDate, Activity, CategoryID,
ROW_NUMBER() over (partition by CategoryID ORDER BY StartDate DESC)ORDERING FROM @tDUMMY
) tmp
WHERE tmp.ORDERING = 1 ORDER BY 1

Result:
1945-01-01 Action in 1945 1
2000-01-01 Chaos 2
2010-10-16 Unknown 3
4712-01-01 Faraway 4
Post #1005651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse