CODEWITHSUNDEEP
sqlsql-server
Anon Ymouse
Anon Ymouse

Reputation: 101

SQL: How do I select only the newest record when selecting multiple records

so I want to take this "returned" set of results and turn it into the one below it based on the created date being the youngest (newest) to indicate the most recent addition.

PhoneNum    sourcetable      FullName         reference        Task                                         CreatedDate
    0   This is not important   Mr john smith   39161   This is different every time, but has been renamed  16/03/2015 15:01:05
    0   This is not important   Mr john smith   39161   This is different every time, but has been renamed  12/03/2015 16:58:22
    0   This is not important   Mr john smith   39161   This is different every time, but has been renamed  12/03/2015 16:58:25
    0   This is not important   Mr john smith   39161   This is different every time, but has been renamed  10/03/2015 12:29:50
    0   This is not important   Mr john smith   39161   This is different every time, but has been renamed  12/03/2015 14:18:47
    0   This is not important   Mr john smith   39161   This is different every time, but has been renamed  10/03/2015 12:40:21
    1   This is not important   Mr wilson smith 39158   This is different every time, but has been renamed  10/03/2015 12:07:14
    1   This is not important   Mr wilson smith 39158   This is different every time, but has been renamed  10/03/2015 12:07:14
    1   This is not important   Mr wilson smith 39158   This is different every time, but has been renamed  10/03/2015 12:07:13

What I need to return:

PhoneNum    sourcetable     FullName       reference      Task                                           CreatedDate
0   This is not important   Mr john smith   39161   This is different every time, but has been renamed  12/03/2015 16:58:25
1   This is not important   Mr wilson smith 39158   This is different every time, but has been renamed  10/03/2015 12:07:14

What I have so far

select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,t.Subject,t.CreatedDate 
from Dial d
join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
where c.FK_ClientID in (39157,39160)

Any help would be appreciated.

Upvotes: 3

Views: 152

Answers (4)

Anthony Johnston
Anthony Johnston

Reputation: 9584

add "order by" to the end to get the order and "desc" to be newest first

ORDER BY t.CreatedDate DESC

add group by to the end

add "top " just after select to limit the number of records returned

SELECT TOP 1 ...

OK try again

SELECT 
    person.PhoneNum,
    person.sourcetable,
    person.FullName,
    person.ref,

    t.Subject,t.CreatedDate 

FROM Database.dbo.Tasks t
JOIN (
    SELECT d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref
    FROM Dial d
    JOIN Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
    JOIN Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
    JOIN Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
    JOIN Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID

    WHERE c.FK_ClientID in (39157,39160)

) person ON T.FK_ApplicationID = person.ref

ORDER BY t.CreatedDate DESC

here, I am selecting the task by newest first and joining to the sub selected people for which there will be one per person

I don't have your db, hope it works or gives you the right steer

Upvotes: 0

Hiten004
Hiten004

Reputation: 2481

Please use the rank function to find the old record, This is not tested!! hope this help

SELECT * FROM (

    select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,t.Subject,t.CreatedDate 
    , RANK() OVER ( PARTITION BY  N.FullName ORDER BY t.CreatedDate DESC ) AS iRank


    from Dial d
    join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
    join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
    join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
    join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
    join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
    where c.FK_ClientID in (39157,39160)

) AS t 
WHERE t.iRank = 1

Upvotes: 2

jeremyb
jeremyb

Reputation: 492

You just have to change it with the last line I added. Select only the most recent date for each client:

select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,t.Subject,t.CreatedDate 
from Dial d
join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
where c.FK_ClientID in (39157,39160)
AND CreatedDate >= ALL (SELECT DISTINCT MAX(CreatedDate) FROM Tasks WHERE PhoneNum = d.PhoneNum)

This way you will get all your clients, but only the last added line for each one.

Upvotes: 0

John Bell
John Bell

Reputation: 2350

You can use ROW_NUMBER():

;WITH CTE AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY REFERENCE ORDER BY CREATEDDATE DESC) AS RN
FROM [TABLE])
SELECT *
FROM CTE
WHERE RN = 1

Obviously you can just change your select statement to get the desired columns.

Upvotes: 8

Related Questions