October 11, 2010 at 12:09 pm
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.
October 11, 2010 at 12:24 pm
How about:
SELECT MAX(Datefield) as MostRecentDate, CategoryID
FROM Table
GROUP BY CategoryID
Or, did I miss something?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 11, 2010 at 12:31 pm
That did it.
Wasn't even thinking about MAX()!!!!
That was the missing link.
I knew there was a simple solution.
Thanks a bunch.:-)
October 11, 2010 at 12:48 pm
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???
October 11, 2010 at 7:31 pm
could you:
SELECT top(1) [ACTIVITIES.StartDateTime]
,ACTIVITIES.CategoryId
,ACTIVITIES.Id
FROM [Activities]
order by [ACTIVITIES.StartDateTime] desc
?
October 12, 2010 at 6:20 am
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
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 15, 2010 at 7:02 pm
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-01Action in 19451
2000-01-01Chaos2
2010-10-16Unknown3
4712-01-01Faraway4
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply