Select statement that gets only the rows with the highest date without using subqueries
-
Trying to see if there is anyway to get the following.
I have a table that stores a company name, an 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.
-
with cte as (Select company,
referral,
referral_date,
row_number() over(partition by company order by referral_date desc) as rn
from referrals)
select company,referral,referral_date
from cte
where rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark, that worked great on my test box... then I forgot we are still running this on SQL Server 2000, so the cte and row_number function wasn't available yet. I posted this in the SQL 2000 forum to see what they say.
-
Continued here: 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply