Select query to return one row per company with the highest date

Question

Post reply

Select query to return one row per company with the highest date

Josh Turley-300197

SSChasing Mays

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

#71112

I accidentally posted this in the 2005 forum. This db is on a 2000 server, so I need a work around that will work on SQL Server 2000.
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.

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Like this?

SET DATEFORMAT MDY

CREATE TABLE #referrals (company VARCHAR(10), referral VARCHAR(20), referral_date DATETIME)

INSERT INTO #referrals (company, referral, referral_date)

SELECT 'Company A', 'Web', '01/01/2006' UNION ALL

SELECT 'Company B', 'Tradeshow', '10/03/05' UNION ALL

SELECT 'Company B', 'Magazine', '12/05/04' UNION ALL

SELECT 'Company C', 'Company A', '09/05/06' UNION ALL

SELECT 'Company C', 'Web', '12/07/07'

SELECT r.*

FROM #referrals r

INNER JOIN (SELECT company, MAX(referral_date) AS MostRecentReferral FROM #referrals GROUP BY company) d

ON d.company = r.company AND d.MostRecentReferral = r.referral_date

ORDER BY r.company

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

SELECT 'Company A', 'Web', '01/01/2006' UNION ALL

SELECT 'Company B', 'Tradeshow', '10/03/05' UNION ALL

SELECT 'Company B', 'Magazine', '12/05/04' UNION ALL

SELECT 'Company C', 'Company A', '09/05/06' UNION ALL

SELECT 'Company C', 'Web', '12/07/07'

Would I have to do this for each row? There's about 2000 just for this last year...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Josh Turley (12/11/2008)
SELECT 'Company A', 'Web', '01/01/2006' UNION ALL
SELECT 'Company B', 'Tradeshow', '10/03/05' UNION ALL
SELECT 'Company B', 'Magazine', '12/05/04' UNION ALL
SELECT 'Company C', 'Company A', '09/05/06' UNION ALL
SELECT 'Company C', 'Web', '12/07/07'
Would I have to do this for each row? There's about 2000 just for this last year...

Nah, that's just to set up some data for testing. I've also used a temporary table (prefixed with #) which will disappear when I close the QA window - you want to use this:

SELECT r.*

FROM referrals r

INNER JOIN (SELECT company, MAX(referral_date) AS MostRecentReferral FROM referrals GROUP BY company) d

ON d.company = r.company AND d.MostRecentReferral = r.referral_date

ORDER BY r.company

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Yeah, I like that better than the way I was doing it. Thanks!