Clever Geek Handbook

SQL: how to select only the newest record when selecting multiple records

so I want to take this "returned" result set and turn it into the one below it based on the generated date being the youngest (newest) one 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 am I still

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.

+3


source to share


4 answers


Please use the rank function to find the old entry, this has not been verified !! 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
+2


source


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 the select statement to get the columns you want.

+8


source


add "order from" to end to get order and "desc" to be last first

ORDER BY t.CreatedDate DESC

add group to end

add "top" immediately after selection 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 choose a task in a new way and join the selected people, for whom there will be one per person

I dont have your db, hope it works or gives you proper control

0


source


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

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 get all of your clients, but only the last added line for each one.

0


source






More articles:


All Articles