Help using DISTINCT function - 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 2005 Forums

Transact-SQL (2005)

Help using DISTINCT function

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2008-10-20 : 10:07:11

I have a query written like this which pulls the most recent DateSaved record for a list of companies. What I really need is to produce a list of records with the most recent DateSaved record for each company.

SELECT
'companies/custom/company_profile.asp?id_company=' + CAST(companies.id_company AS varchar(10)) AS URL,
Companies.CompanyName,
Companies.Ticker,
MAX(TTrackSearchResults.DateSaved) As DateSaved
FROM
TTrackSearchResults LEFT JOIN Companies ON TTrackSearchResults.SqlText = Companies.Ticker

WHERE
((userid=1)

AND (SearchType='Company Profile')

GROUP BY
Companies.id_company,
Companies.CompanyName,
Companies.Ticker,
TTrackSearchResults.DateSaved
ORDER BY DateSaved DESC

I tried adding DISTINCT(Companies.Ticker) but that's generating an error.

SELECT
'companies/custom/company_profile.asp?id_company=' + CAST(companies.id_company AS varchar(10)) AS URL,
Companies.CompanyName,
DISTINCT(Companies.Ticker),
MAX(TTrackSearchResults.DateSaved) As DateSaved

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 10:19:27

[code]SELECT
'companies/custom/company_profile.asp?id_company=' + CAST(companies.id_company AS varchar(10)) AS URL,
Companies.CompanyName,
Companies.Ticker,
t.DateSaved
FROM
Companies
LEFT JOIN (SELECT SqlText,MAX(TTrackSearchResults.DateSaved) As DateSaved
FROM TTrackSearchResults
GROUP BY SqlText)t
ON t.SqlText = Companies.Ticker [/code]