Select query that only returns the rows with the highest date

Question

Select query that only returns the rows with the highest date

Josh Turley-300197

SSChasing Mays

Points: 660
More actions
December 11, 2008 at 8:23 am

#128356

I posted this in the noob forum too but I figured you might have better answers in here.
Trying to see if there is anyway to get the following.
I have a table that stores a company name, a referral source, and the date that company was referred. A company can have multiple referral sources as they might check out our product a few different times before purchasing. I'm looking to only show the last referral date though, since that would be the last time they looked at our product before committing to purchase it.
A basic select script would look like this:
Select company, referral, referral_date
from referrals
A sample would be:
Company A Web 01/01/2006....
Company B Tradeshow 10/03/05
Company B Magazine 12/05/04
Company C Company A 09/05/06
Company C Web 12/07/07
So to do this, I came up with the following, but I feel like there is a better way.
Select r1.company,
case r1.referral_date
when (select max(referral_date) from referrals r2 where r2.company = r1.company)
then r1.referral
end,
r1.referral_date
from referrals
If you know of a better way to do this that is either a: cleaner code or b: not as resource intensive (since it's having to run more than 1 query) please let me know.
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Josh Turley-300197 SSChasing Mays Points: 660 More actions · Answer 1

Never mind... mark gave me a good solution using CTE and row_number() but I forgot this was on a 2000 box and not a 2005. I posted this in the other forum though if anyone has any suggestions that will work with 2000.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Josh Turley (12/11/2008)
I posted this in the noob forum too but I figured you might have better answers in here.

Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic617982-8-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass