Help with an MS SQL Server Query to return the latest dates against each recordID.

[DaveHayzen]

The following SQL query :-

SELECT CardHolder.RecordID, History.GenTime, History.Link1
FROM History FULL OUTER JOIN
Card ON History.Param3 =
LTRIM(RTRIM(Card.CardNumber)) FULL OUTER JOIN
CardHolder ON Card.CardHolderID =
CardHolder.RecordID
WHERE (Card.Deleted = 0) AND (History.GenTime IS NOT NULL)
ORDER BY CardHolder.RecordID, History.GenTime DESC

returns :-

RecordID GenTime Link1

2 04/06/2004 15:30:00 123
2 01/06/2004 16:00:00 123
2 01/06/2004 08:00:00 110
1155 02/06/2004 11:30:00 123
1155 02/06/2004 08:00:00 110
3925 03/06/2004 09:00:00 123
3925 03/06/2004 08:00:00 110
4511 06/06/2004 11:30:00 123
4511 06/06/2004 10:30:00 110

Is there a way of modifying this query to just return the lastest
genTime for each RecordID ??? ie return just rows 1,4,6 & 8.

I assume it is something to do with MAX, but I can't seem to get my
head around it.

Any help, or pointers would be appreciated.

Oh, running query on Microsoft SQL Server 2000.

Regards
Dave

[David Portas]

Here's one possible method (untested). Note that I've changed the first of
your joins to INNER because your WHERE clause makes the FULL JOIN redundant.

SELECT CH.recordid, H.gentime, H.link1
FROM History AS H
JOIN Card AS C
ON H.param3 = LTRIM(RTRIM(C.cardnumber))
AND H.gentime =
(SELECT MAX(gentime)
FROM History
WHERE param3 = H.param3)
FULL OUTER JOIN CardHolder AS CH
ON C.cardholderid = CH.recordid
WHERE C.deleted = 0
AND H.gentime IS NOT NULL
ORDER BY cardholder.recordid, H.gentime DESC

--
David Portas
SQL Server MVP
--

[David Hayzen]

David,

Many many thanks for you help - that did the trick.

Simple now that I've seen how you've approached it.

Regards,

Dave

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Bell]

Hi

Having Card Holder in this seems to be redundant!

This will also assume that H.GenTime is unique..

SELECT C.CardHolderID, H.GenTime, H.Link1
FROM History H
FULL OUTER JOIN Card C ON H.Param3 = LTRIM(RTRIM(C.CardNumber))
WHERE C.Deleted = 0
AND H.GenTime IS NOT NULL
AND H.GenTime = ( SELECT MAX(I.GenTime) FROM History I WHERE I.Param3 =
H.Param3 )
ORDER BY C.CardHolderID, H.GenTime DESC

John

"DaveHayzen" <dh*****@ersltd.co.uk> wrote in message
news:f6**************************@posting.google.c om...

The following SQL query :-

SELECT CardHolder.RecordID, History.GenTime, History.Link1
FROM History FULL OUTER JOIN
Card ON History.Param3 =
LTRIM(RTRIM(Card.CardNumber)) FULL OUTER JOIN
CardHolder ON Card.CardHolderID =
CardHolder.RecordID
WHERE (Card.Deleted = 0) AND (History.GenTime IS NOT NULL)
ORDER BY CardHolder.RecordID, History.GenTime DESC

returns :-

RecordID GenTime Link1

2 04/06/2004 15:30:00 123
2 01/06/2004 16:00:00 123
2 01/06/2004 08:00:00 110
1155 02/06/2004 11:30:00 123
1155 02/06/2004 08:00:00 110
3925 03/06/2004 09:00:00 123
3925 03/06/2004 08:00:00 110
4511 06/06/2004 11:30:00 123
4511 06/06/2004 10:30:00 110

Is there a way of modifying this query to just return the lastest
genTime for each RecordID ??? ie return just rows 1,4,6 & 8.

I assume it is something to do with MAX, but I can't seem to get my
head around it.

Any help, or pointers would be appreciated.

Oh, running query on Microsoft SQL Server 2000.

Regards
Dave