[RESOLVED] Help needed with SQL Statement

[Bill Crawley]

Hi All,

So far I have the following SQL:

select c.sorid, c.startdate, c.cost
from
dbo.SORContractorCost c
where
c.startdate <= getdate()
and
c.contractorid = 1
order by
c.sorid, c.startdate desc

This returns me data in the form:

3436 2007-08-11 00:00:00.000 3.33
3437 2007-08-11 00:00:00.000 570.00
3438 2007-08-11 00:00:00.000 0.48
3439 2007-08-12 00:00:00.000 0.50
3439 2007-08-11 00:00:00.000 0.48
3440 2007-08-11 00:00:00.000 39.66
3442 2007-08-11 00:00:00.000 200.06
3443 2007-08-11 00:00:00.000 0.48
3444 2007-08-11 00:00:00.000 11.40

The problem I have is that There are 2 records with sorid 3439. ( not a problem with the data), but I only want to return the Latest of these 2 records i.e. the 12th. How should I change the above statement to achieve this? So I want to return a single record for a sorcode based on the latest date <= today (current record in use) those with earlier dates are there for historical reasons.

[erickwidya]

try this one
SELECT c.sorid, c.startdate, c.cost
FROM table2 AS c
WHERE c.startdate <= getdate() AND c.contractorid = 1
GROUP BY C.Sorid, c.startdate, c.cost
HAVING c.StartDate = (SELECT MAX(t2.StartDate) FROM table2 as t2 WHERE t2.sorid = c.sorid)
ORDER BY c.sorid, c.startdate DESC

[Bill Crawley]

This didn't work :( it's removed both records with 3439, instead of leaving the one dated 12th in the selection.

[Bill Crawley]

Thanks for getting me on the right track :).

This seems to have resolved my problem:

SELECT c.sorid, c.startdate, c.cost
FROM dbo.SORContractorCost AS c
WHERE c.startdate <= getdate() AND c.contractorid = 1
and c.startdate = (SELECT MAX(t2.StartDate) FROM dbo.SORContractorCost as t2 WHERE t2.sorid = c.sorid and t2.startdate <= getdate())
ORDER BY c.sorid, c.startdate DESC