x
Solved

# SQL Max Record

Posted on 2014-03-18
Medium Priority
375 Views
I have this query:
``````SELECT        pr.PartNum, pr.RevisionNum, pr.EffectiveDate, pr.PartNum + '*' + pr.RevisionNum AS PartNumRevisionNum
FROM            dbo.PartRev AS pr INNER JOIN
(SELECT        PartNum, MAX(EffectiveDate) AS maxeffdate
FROM            dbo.PartRev AS b
WHERE        (Approved = 1) AND (EffectiveDate <= GETDATE())
GROUP BY PartNum) AS b_1 ON b_1.PartNum = pr.PartNum AND b_1.maxeffdate = pr.EffectiveDate
WHERE        (pr.Approved = 1)
``````

It is returning:
1234 A 07-27-14
3456 B 08-12-14
4567 B 09-01-14
4567 C 09-01-14

I want it to return:
1234 A 07-27-14
3456 B 08-12-14
4567 C 09-01-14

How would I revise this to get this result.
0
Question by:maverick0728

LVL 143

Expert Comment

ID: 39937187
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 39937189
SELECT partnum,
revisionnum,
effectivedate,
partnum + '*' + pr.revisionnum AS partnumrevisionnum
FROM (SELECT partnum,
revisionnum,
effectivedate,
ROW_NUMBER() OVER(PARTITION BY partnum ORDER BY effectivedate) AS rn
FROM dbo.partrev
WHERE approved = 1 AND effectivedate <= getdate()) as x
WHERE rn = 1
0

LVL 70

Expert Comment

ID: 39937262
SELECT        pr.PartNum, pr.RevisionNum, pr.EffectiveDate, pr.PartNum + '*' + pr.RevisionNum AS PartNumRevisionNum
FROM            dbo.PartRev AS pr INNER JOIN
(SELECT        PartNum, MIN(EffectiveDate) AS maxeffdate
FROM            dbo.PartRev AS b
WHERE        (Approved = 1) AND (EffectiveDate <= GETDATE())
GROUP BY PartNum) AS b_1 ON b_1.PartNum = pr.PartNum AND b_1.maxeffdate = pr.EffectiveDate
WHERE        (pr.Approved = 1)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.