T-SQL Query help

[boatie]

Hi All,

I am (attempting to) write a select query where I want to return distinct rows based on a max/distinct criteria.

please see the below query for my WIP thus far:

select lintitemnumber as IPN, curcost as CurCost, dtmwhen as CurCostDate
from podetail pod
where dtmwhen = (select max(dtmwhen) from podetail pod2 where pod.lintitemnumber = pod2.lintitemnumber)
order by lintitemnumber

I am only seeking to return distinct lintitemnumbers, with curcosts that correspond with the max 'dtmwhen' for that row

FYI. the table has 5 columns, three of which are the 'lintitemnumber', 'curcost' and 'dtmwhen' columns.

any assistance would be greatly appreciated.

 

[Transcend]

select lintitemnumber as IPN, curcost as CurCost, dtmwhen as CurCostDate
from podetail pod
where dtmwhen = (select max(dtmwhen) from podetail pod2 where pod.lintitemnumber = pod2.lintitemnumber)
group by lintitemnumber

try this?

without knowing what your data looks like it's hard to tell

Transcend

order by lintitemnumber

 

[Transcend]

or maybe the group by should be in the sub select ...
tell you what
i'll go get some sleep
then look at it again when my brain is working!

Anyone else?

Transcend

 

[SonOfEmidec1100]

Is this getting close to your needs

SELECT podetail.lintitemnumber AS IPN, podetail.curcost AS CurCost, podetail.dtmwhen AS CurCostDate
FROM (SELECT lintitemnumber, MAX(dtmwhen) AS dtmwhen
FROM podetail
GROUP BY lintitemnumber) DERIVEDTBL INNER JOIN
podetail ON podetail.lintitemnumber = DERIVEDTBL.lintitemnumber AND podetail.dtmwhen = DERIVEDTBL.dtmwhen
ORDER BY podetail.lintitemnumber

 

[boatie]

Hi Sonof....

Thanks for the refinement.

I made just 1 subtle change (adding the distinct to the initial select podetail.lintitemnumber, and it was EXACTLY what I was after.

many thanks to you for your reply.

P.S thanks to Transcend too for your input.

SELECT DISTINCT podetail.lintitemnumber AS IPN, podetail.curcost AS CurCost, podetail.dtmwhen AS CurCostDate
FROM (SELECT lintitemnumber, MAX(dtmwhen) AS dtmwhen FROM podetail GROUP BY lintitemnumber)
DERIVEDTBL INNER JOIN podetail ON podetail.lintitemnumber = DERIVEDTBL.lintitemnumber AND podetail.dtmwhen = DERIVEDTBL.dtmwhen
ORDER BY podetail.lintitemnumber


regards

Boatie