TSQL - Use MAX (Date) and return all columns

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 2000 Forums

Transact-SQL (2000)

TSQL - Use MAX (Date) and return all columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-23 : 08:51:06

Graham writes "Get ALL the columns from a table, where the record is active.

Table:SAL_ProdComm
ProductCode,CorpID,ConsultantTypeID,DateFrom,HiVal,LoVal

An Active record is defined as a ProductCode and ConsultantID with the latest (max) Date.

This Gives me the desired Result:
SELECT ProductCode, ConsultantTypeID, MAX(DateFrom) AS DteFrom
FROM SAL_ProdComm
WHERE (CorpID = 23)
GROUP BY ProductCode, ConsultantTypeID

However I need to return all the columns including the HiVal & LowVal Columns in the result.

When I try this below, I get inconsistent results, by the looks of it, if there is only one record for the combination, it is not returned.

Select ProductCode,ConsultantTypeID, DateFrom, corpid, HiVal,LoVal from Sal_ProdComm r Join
(Select ProductCode as PC,Max(DateFrom) as DDate, ConsultantTypeID as CT From Sal_ProdComm
Group by ProductCode, consultantTypeID)a on r.productcode=a.pC and r.datefrom=a.ddate and r.ConsultantTypeId=a.CT where Corpid=23

Many Thanks
Graham
---------------
Cape Town
South Africa"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-23 : 09:13:46

Put the where CorpID = 23 in your derived table and try that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources