CODEWITHSUNDEEP
sqlsql-servert-sql
pee2pee
pee2pee

Reputation: 3792

Selecting Max Value for an Existing Query

firstly, here's my code

SELECT dbo.tblPat.pID, dbo.tblRec.rID,
right(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) as pCheck,
right(rGP, LEN(rGP) - (CHARINDEX('|', rGP))) as GPCheck
FROM
dbo.tblPat INNER JOIN
dbo.tblPatRecords ON dbo.tblPat.pID = dbo.tblPatRecords.pID INNER JOIN
dbo.tblRec ON dbo.tblPatRecords.rID = dbo.tblRec.rID
WHERE     
(DATEPART(dw, dbo.tblRec.rDateRecAdd) IN (5, 6, 7)) 
AND (dbo.tblRec.rGP <> N'0') 
AND (dbo.tblRec.rPCheck <> N'0')
AND right(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) > right(rGP, LEN(rGP) - (CHARINDEX('|', rGP)))
ORDER BY dbo.tblPat.pID, dbo.tblRec.rID

Whih basicallybrings back data in this format

39  366 26/06/2008 16:54:35 26/06/2008 15:04:53
39  368 27/06/2008 09:33:55 27/06/2008 08:57:07
39  369 27/06/2008 09:35:14 27/06/2008 08:57:07
39  370 27/06/2008 09:36:34 27/06/2008 08:57:07
39  371 27/06/2008 09:37:33 27/06/2008 08:57:07
39  372 27/06/2008 09:37:33 27/06/2008 08:57:07
39  373 27/06/2008 09:37:33 27/06/2008 08:57:07
39  374 27/06/2008 09:37:33 27/06/2008 08:57:07
*39 397 27/06/2008 13:13:49 27/06/2008 12:48:25*
92  46310   19/08/2008 15:52:50 03/10/2008 14:50:00
92  46313   19/08/2008 15:52:50 03/10/2008 14:52:50
92  46315   19/08/2008 15:52:50 03/10/2008 14:52:50
92  46558   19/08/2008 15:52:50 03/10/2008 19:26:04
92  46559   19/08/2008 15:52:50 03/10/2008 19:26:04
92  46623   04/10/2008 10:46:39 03/10/2008 19:26:04
*92 46632   04/10/2008 10:46:39 03/10/2008 19:26:04*

As you may have guessedm I need to pick out the last entry for each pID so in this case, I need

39  397 27/06/2008 13:13:49 27/06/2008 12:48:25
92  46632   04/10/2008 10:46:39 03/10/2008 19:26:04

Any help would be great. I could cheat and turn this into a view and then do another view but I want to be as clean as possible

Upvotes: 1

Views: 128

Answers (2)

Lamak
Lamak

Reputation: 70638

If you are using SQL Server 2005 or 2008, then you can use the ROW_NUMBER function. Try this:

WITH Results AS (
SELECT  dbo.tblPat.pID, dbo.tblRec.rID,
        RIGHT(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) as pCheck,
        RIGHT(rGP, LEN(rGP) - (CHARINDEX('|', rGP))) as GPCheck,
        ROW_NUMBER() OVER(PARTITION BY dbo.tblPat.pID ORDER BY dbo.tblRec.rID DESC) Id
FROM dbo.tblPat 
INNER JOIN dbo.tblPatRecords 
ON dbo.tblPat.pID = dbo.tblPatRecords.pID 
INNER JOIN dbo.tblRec 
ON dbo.tblPatRecords.rID = dbo.tblRec.rID
WHERE (DATEPART(dw, dbo.tblRec.rDateRecAdd) IN (5, 6, 7)) 
AND (dbo.tblRec.rGP <> N'0') 
AND (dbo.tblRec.rPCheck <> N'0')
AND right(rPCheck, LEN(rPCheck) - (CHARINDEX('|', rPCheck))) > right(rGP, LEN(rGP) - (CHARINDEX('|', rGP)))
)

SELECT *
FROM Results
WHERE Id = 1

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147224

Instead of the JOIN onto tblPatRecords directly, try replacing with a join onto a subquery, so e.g. replace your FROM clause to this:

FROM dbo.tblPat INNER JOIN
(
    SELECT pID, MAX(rID) AS rID
    FROM dbo.tblPatRecords
    GROUP BY pID
) t ON dbo.tblPat.pID = t.pID
INNER JOIN dbo.tblRec ON t.rID = dbo.tblRec.rID

Upvotes: 3

Related Questions