?
Solved

SQL Max Record

Posted on 2014-03-18
3
Medium Priority
?
357 Views
Last Modified: 2014-03-18
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)

Open in new window


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
Comment
Question by:maverick0728
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39937187
0
 
LVL 74

Accepted Solution

by:
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 69

Expert Comment

by:Scott Pletcher
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question