I have a table which contains data on a series of events in an MSSQL database:
ID Name Date Location Owner
--- --------------------------------------------------------- ----------- -------------------------------- -----------
1 Seminar Name 1 2013-08-08 A Location Name 16
2 Another Event Name 2013-07-30 Another Location 18
3 Event Title 2013-08-21 Head Office 94
4 Another Title 2013-08-30 London Office 18
5 Seminar Name 2 2013-08-27 Town Hall 19
6 Title 2013-08-20 Somewhere Else 196
7 Fake Seminar For Testing 2013-08-25 Fake Location 196
Hopefully you can see that this table contains a number of events which are owned by several users in our application. I am trying to figure out if there is a query I can use to select the most recently occurring event for each user. I think the easiest way to show what I want is to show the ideal result table I'm looking for (based on today's date):
ID Name Date Location Owner
--- --------------------------------------------------------- ----------- -------------------------------- -----------
1 Seminar Name 1 2013-08-08 A Location Name 16
2 Another Event Name 2013-07-30 Another Location 18
3 Event Title 2013-08-21 Head Office 94
5 Seminar Name 2 2013-08-27 Town Hall 19
6 Title 2013-08-20 Somewhere Else 196
The best I could come up with at the moment is this query:
SELECT DISTINCT Owner, Date, ID FROM Seminars
GROUP BY Owner, Date, ID ORDER BY Date
It doesn't really do what I want to do and I think the real solution is going to be a bit more complex than this as I need to somehow select based to today's date too.
WITH CTE
AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Owner
ORDER BY Date DESC) AS RN
FROM tablename
)
SELECT ID, Name, Date, Location, Owner
FROM CTE
WHERE RN = 1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With