CODEWITHSUNDEEP
sqlsql-serverselectdistinct
edcs
edcs

Reputation: 3879

How To Select Distinct Row Based On Multiple Fields

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.

Upvotes: 4

Views: 26579

Answers (4)

SGrebenkin
SGrebenkin

Reputation: 572

The following query might help. The only problem is when you have two seminars with the same date for the given owner. In this case, the query will return all of the seminars for the owner on this 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

Upvotes: 0

user2572367
user2572367

Reputation: 207

select * from seminars order by datediff(SemindarDate,getDate) desc

Upvotes: 0

Manos
Manos

Reputation: 122

does something simple like this work for you?

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

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

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;

Upvotes: 9

Related Questions