Getting Top 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting Top 1

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-09-21 : 15:37:53

Hi There,
I am looking for a little help.

if I have a table of records with duplicate refnumbers
How do I return the latest refnumber for that refnumber by date. so if I have a table of records:
Ref | Date | Type
2 | 2011-07-20 | Call
3 | 2011-07-18 | Call
2 | 2011-07-21 | Call
4 | 2011-07-19 | Return
2 | 2011-07-22 | Return
3 | 2011-07-20 | Return
4 | 2011-07-20 | Call
4 | 2011-07-23 | Call

And the query would return:
Ref | Date | Type
2 | 2011-07-22 | Return
3 | 2011-07-20 | Return
4 | 2011-07-23 | Call

Thanks for your help, I appreciate it.

Best Regards

Steve

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 15:47:30

Hello,

Perhaps something like;




SELECT t.Ref, d.[Date], t.[Type]
FROM <your table> t
JOIN
(
	SELECT Ref,MAX([date]) AS [Date]
	FROM <your table>
	GROUP BY Ref
) d ON d.Ref = t.Ref AND d.[Date] = t.[Date]

HTH.

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-09-21 : 15:49:03

Select Max(Date), Ref, Type
From Table
Group By Ref, type, Date

[ehorn is on it! Dang!]

Everyday life brings me back to reality

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-09-21 : 16:07:04

Hey,

That works great.

Thanks a lot for your very quick replies.

Best Regards,

Steve

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 16:10:43

yvw Steve,

Have a nice day.

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-09-21 : 16:33:13

Hi There,

Sorry, I need your help again.
The query returns exactly what I want but I need to do a little work based on the value returned in the Type field.
If the type = "Call" then I need a column called "response" to contain the value "Return a Call"
If the type = "Return" then I need a column called "response" to contain the value "Do Not Call Again"

Thanks again for you help.

Best Regards,

Steve

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-09-21 : 17:22:45

What about this:

Case when type = "Call" then "Return a Call"
when type = "Return" then "Do Not Call Again"
End Else Null End as 'Response'

Everyday life brings me back to reality

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources