CODEWITHSUNDEEP
sql-server-2008
Hari Gillala
Hari Gillala

Reputation: 11916

SQL Distinct rows based on date created the row

I have the following data.

enter image description here

I need to query the data so that I will get only the following records.

I need unique records based on the DateApplication. Like the below.

Can anybody tell me how to do this please in SQL server 2008?

enter image description here

Upvotes: 1

Views: 881

Answers (5)

aF.
aF.

Reputation: 66697

Assuming that you want the last entry, you can do it like this:

SELECT t1.Name, t1.Email, t1.DateApplication, t1.Description
FROM tablename t1
WHERE t1.DateApplication = 
      (SELECT MAX(t2.DateApplication)
       FROM tablename t2
       WHERE t2.Name = t1.Name AND t2.Email = t1.Email)

Upvotes: 1

Magnus
Magnus

Reputation: 46947

SELECT Name, email, MAX(DateApplication) As DateApplication, 
  (SELECT TOP 1 description
  FROM tbl As tbl1 
  where tbl1.Email = tbl.Email AND tbl1.DateApplication = MAX(tbl.DateApplication))
  ) As description
from tbl
GROUP BY Name, email

Upvotes: 2

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

You can try this

SELECT Name, Email, DateApplication, Description
FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY DateApplication DESC) RowNumber,
              Name, Email, DateApplication, Description
       FROM YourTableName)
WHERE RowNumber = 1

Hope this helps

Upvotes: 2

user596075
user596075

Reputation:

You could utilize the row_number() function here:

select
    name,
    email,
    dateapplication,
    description
from
(
    select *, row_number() over (partition by email order by dateapplication desc) as row_num
    from YourTable
)a
where row_num = 1

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58451

There are more than one way to skin a cat but typically, you have a subquery returning you each Name with it's latest date and JOIN this back with your original table.

Using a WITH statement

;WITH q AS (
  SELECT  Name, DateApplication = MAX(DateApplication)
  FROM    YourData
  GROUP BY
          Name
)
SELECT  Name
        , Email
        , DateApplication
        , Description
FROM    YourTable yt
        INNER JOIN q ON q.Name = yt.Name
                        AND q.DateApplication = yt.DateApplication

Using an INNER JOIN

SELECT  Name
        , Email
        , DateApplication
        , Description
FROM    YourTable yt
        INNER JOIN (
          SELECT  Name, DateApplication = MAX(DateApplication)
          FROM    YourData
          GROUP BY
                  Name
        ) q ON q.Name = yt.Name
               AND q.DateApplication = yt.DateApplication

Note that in each of these examples I assume that the combination Name, DateApplication is unique.

Upvotes: 2

Related Questions