How to select a single row based on several fields

Question

I have a table that contains data about a series of events in the 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 

I hope you see that this table contains a series of events that belong to several users in our application. I am trying to find out if there is a query that I can use to select the last 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 thing I could think of at the moment is this query:

 SELECT DISTINCT Owner, Date, ID FROM Seminars GROUP BY Owner, Date, ID ORDER BY Date 

This is really not what I want to do, and I think that the real solution will be a little more complicated than that, since I need to somehow choose based on today's date too.

Answer 1

 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; 

Answer 2

does something simple, how is this work for you?

 SELECT DISTINCT ID, CreatedBy, SeminarDate FROM CUSTOMERDB.dbo.Seminars ORDER BY SeminarDate DESC 

Answer 3

select * from the list of workshops by fariff (SemindarDate, getDate) desc

Answer 4

This query may help. The only problem is that you have two seminars with the same date for this owner. In this case, the request will return all the workshops for the owner on that date.

 ;WITH T AS ( SELECT MAX(Date) AS Date, Owner FROM Seminars GROUP BY Owner ) SELECT S.* FROM T INNER JOIN Seminars S ON T.Owner=S.Owner AND T.Date=S.Date ORDER BY S.Date