SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query of last records(timestamp) for Distinct CategoryId's in same table.


SQL Query of last records(timestamp) for Distinct CategoryId's in same table.

Author
Message
slacoste
slacoste
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
Michael L John
Michael L John
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2927 Visits: 7787
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/
slacoste
slacoste
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.:-)
slacoste
slacoste
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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???
lukus_g
lukus_g
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 80
could you:

SELECT top(1) [ACTIVITIES.StartDateTime]
,ACTIVITIES.CategoryId
,ACTIVITIES.Id

FROM [Activities]

order by [ACTIVITIES.StartDateTime] desc


?
Michael L John
Michael L John
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2927 Visits: 7787
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/
VinceV
VinceV
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search