How To Select Distinct Row Based On Multiple Fields

Question

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.

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;