CODEWITHSUNDEEP
sqlsql-server
hopper
hopper

Reputation: 4338

SQL - Select Top 1 multiple lines of the same id

I have the following query:

select * from [lead].[ContactFeedback] cf
where cf.LeadId in
(select LeadId from [lead].[LeadDetails]
where TeleportReference in (122096, 
122097, 
122098))
order by LeadId Desc

The results are something like this :

FeedbackDate            LeadId         
2015-01-23 16:25:13.547 95920
2015-01-23 16:25:38.960 95919
2015-01-23 16:25:19.393 95917
2015-01-23 16:25:32.837 95916
2015-01-23 16:25:59.840 95914
2015-01-23 16:26:08.840 95913
2015-01-23 16:15:01.933 95910
2015-01-23 16:22:04.820 95910
2015-01-23 16:24:40.477 95909
2015-01-23 16:24:03.523 95908
2015-01-23 16:16:44.290 95908
2015-01-23 16:17:16.047 95907
2015-01-23 16:25:11.783 95907

I want to list all top 1(most recent feedbackdate) for each LeadId. How can I achieve this in SQL Server?

Upvotes: 0

Views: 2062

Answers (3)

JBrooks
JBrooks

Reputation: 10013

You can do a sub query to get the max FeedBackDate for each LeadId. Then join the detail table to that.

select cf.* 
from [lead].[ContactFeedback] cf
join (select LeadId, max(FeedBackDate) maxFeedBackDate
     from [lead].[ContactFeedback]
     group by LeadId) as maxes
on cf.LeadId = maxes.LeadId
   and cf.FeedBackDate = maxes.maxFeedBackDate
where cf.LeadId in
      (select LeadId 
       from [lead].[LeadDetails]
       where TeleportReference in (122096, 
                                   122097, 
                                   122098)
       )
order by cf.LeadId Desc

Upvotes: 0

ASh
ASh

Reputation: 35681

select LeadId, Max(FeedbackDate) 
from [lead].[ContactFeedback] cf
where cf.LeadId in
    (select LeadId from [lead].[LeadDetails]
     where TeleportReference in (122096, 122097, 122098))
group by LeadId
order by LeadId Desc

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use Window Function 

select leadid,feedbackdate from ( 
select row_number() over(partition by leadid order by feedbackdate desc) rn,* 
from yourtable) a
where rn=1

Upvotes: 0

Related Questions