SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

Frijado_ad
Starting Member

2 Posts

Posted - 2010-11-26 : 04:19:23

Hi .

I've been struggeling with an SQL query.
I have an table which contains eventsid , datetime and status.
The eventid is an eventnumber that represents an specific sort of event.
The datetime is the datetime value when a specific event occurred.
The status is a value (1,2,3,4 or 5)

The table records all eventchanges, thus when an event occurs , it add a record to the table (eventid, datetime en status)

The query I would like is:
- Count the number of records For each status value
for every Unique eventnumber
that has the most recent datetime value

Thus in short.
For every unique event number with the most recent datetime show me the status and count all status whith the same eventnumber.

Thanks

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-26 : 04:55:21

Try this -



SELECT E.EventsID, COUNT(E.Status) 
FROM Events E
INNER JOIN 
(
SELECT EventsID, ROW_NUMBER() OVER( PARTITION BY EventsID ORDER BY Date DESC ) RowNo FROM Events
) A ON E.EventsID = A.EventsID AND RowNo = 1
GROUP BY E.EventsID

Vaibhav T

If I cant go back, I want to go fast...