CODEWITHSUNDEEP
sql-server
Denslat
Denslat

Reputation: 179

SQL: Return multiple oldest records only

I'm attempting to return a string using a complicated bit of sql, but I just cant get it to return based on the same phonenumber with the newest D.CreateDateTime, for instance

Source           CallData                       Phonenum    FK_REF  D.createdatetime
Source   1 609 ^Mr Richard Smith^01234567891^   01234567891 27657   16/06/2014
Source2  1 609 ^Mr Richard Smith^01234567891^   01234567891 27657   21/07/2014
Source3  1 609 ^Mr Richard Smith^01234567891^   01234567891 27657   03/10/2014

Expected Result

    Source           CallData                       Phonenum    FK_REF  D.createdatetime
    Source3  1 609 ^Mr Richard Smith^01234567891^   01234567891 27657   03/10/2014

However I can't seem to find a way to do this effectively, as attempts using

SQL Server: SELECT only the rows with MAX(DATE) and T-SQL select rows by oldest date and unique category

Does not work, or rather I can't find a place to insert said data into my query.

If someone can understand the (somewhat complicated) sql query enough to help, it would be beyond amazing.

Thank you

Code below

--Create Table #tmp2(FK_clientids varchar(50))
--Create table #tmp (phonenums varchar(50))
Delete from #tmp2
Delete from #tmp
Use DavikerTWF_MTA
INSERT INTO #tmp2
SELECT fk_clientid
FROM DM_ClientApplicants
where FK_ApplicationID in (--FK_ApplicationID goes here)

Use DavikerTWF_OTS
INSERT INTO #tmp
Select phonenum2 from DM_PhoneNumbers
where FK_ApplicationID in 
(
   Select FK_clientIDs from #tmp2
)
INSERT INTO #tmp
Select PhoneNum1 from DM_PhoneNumbers
where FK_ApplicationID in 
(
   Select FK_clientIDs from #tmp2
)
INSERT INTO #tmp
Select PhoneNum2 from DM_PhoneNumbers
where FK_ApplicationID in 
(
   Select FK_clientIDs from #tmp2
)

INSERT INTO #tmp
Select PhoneNum3 from DM_PhoneNumbers
where FK_ApplicationID in 
(
   Select FK_clientIDs from #tmp2
)
INSERT INTO #tmp
Select Partnerphonehome from DM_PhoneNumbers
where FK_ApplicationID in 
(
   Select FK_clientIDs from #tmp2
)
INSERT INTO #tmp
Select Partnerphonemobile from DM_PhoneNumbers
where FK_ApplicationID in 
(
   Select FK_clientIDs from #tmp2
)
INSERT INTO #tmp
Select Partnerphonework from DM_PhoneNumbers
where FK_ApplicationID in 
(
   Select FK_clientIDs from #tmp2
)
Use TouchStar
Select sourcetable,CallData,PhoneNum,DM_PhoneNumbers.FK_ApplicationID,d.CreateDateTime from Dial D
join DavikerTWF_OTS.dbo.DM_PhoneNumbers on PhoneNum= PhoneNum1
Collate latin1_general_CI_AS
where PhoneNum in
(
Select phonenums from #tmp
)

Upvotes: 0

Views: 155

Answers (2)

Brian Pressler
Brian Pressler

Reputation: 6713

There are a few ways you can do this. The way I often do it is to use the row_number function to order the rows partitioned by the key you are interested in, and then select only the top one. Like this:

select sourcetable,CallData,PhoneNum,FK_ApplicationID,CreateDateTime
from
(
    Select sourcetable,CallData,PhoneNum,DM_PhoneNumbers.FK_ApplicationID,d.CreateDateTime, ROW_NUMBER() Over (Partition by PhoneNum order by d.CreateDateTime desc) rownum
    from Dial D
    join DavikerTWF_OTS.dbo.DM_PhoneNumbers 
    on PhoneNum= PhoneNum1 Collate latin1_general_CI_AS
    where PhoneNum in
        (
        Select phonenums from #tmp
        )
) AllRows
where rownum = 1

Upvotes: 2

M.Ali
M.Ali

Reputation: 69494

Try something like this...

;WITH CTE AS 
(SELECT *
      ,ROW_NUMBER() OVER (PARTITION BY Phonenum ORDER BY
              CAST(RIGHT(createdatetime ,4)+SUBSTRING(createdatetime, 4,2)+LEFT(createdatetime ,2) 
                   AS DATETIME) DESC) rn
FROM TableName   
)
SELECT * FROM CTE 
WHERE rn = 1

Upvotes: 1

Related Questions