March 5, 2008 at 11:36 am
How can I easily pull out the uniqueID for the latest revision on each of the SNs in the table below? (in this case the answer would be 4 and 6)
UniqueIDSNRevisionNumber
1GM010
2GM020
3GM021
4GM011
5GM022
6GM023
March 5, 2008 at 11:45 am
;with LastRev (SN1, Rev) as
(select SN, max(RevisionNumber)
from dbo.table)
select UniqueID, SN, RevisionNumber
from dbo.table
inner join LastRev
on sn = sn1
and revisionnumber = rev
Try that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 5, 2008 at 1:14 pm
Without using a CTE this can be accomplished with the following...
select uniqueID from t1 join
( select SN, max(RevisionNUmber) rn from group by SN ) v on
v.SN = t1.SN and v.rn = t1.RevisionNumber
DAB
March 5, 2008 at 1:19 pm
CTE... Derivied table... no difference in performance, memory usage, of disk hits on TempDB... they both equate to "inline views"...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2008 at 2:02 pm
Thanks all!
Being a newbie at this I stand to learn something from each of your replies. No surprise that there are several ways to accomplish the task.
March 5, 2008 at 2:09 pm
fvandenbosch (3/5/2008)
Thanks all!Being a newbie at this I stand to learn something from each of your replies. No surprise that there are several ways to accomplish the task.
You are correct in that there are usually several ways to solve a problem. However, when faced with multiple solutions try and examine not only which one best fits your requirements but also for performance. As Jeff mentioned these are virtually the same. But for any solution set the Show Estimated Execution Plan, SET STATISTICS IO ON and display client stats. These will give you a lot of insight as to what is "going on under the hood" during execution.
DAB
March 5, 2008 at 2:46 pm
fvandenbosch (3/5/2008)
Thanks all!Being a newbie at this I stand to learn something from each of your replies. No surprise that there are several ways to accomplish the task.
Ok then... let's do it right for the new guy...
The first 2 sections in the code below is what most folks would like to see when you identify a problem that you'd like to have solved on this forum. See my signature for a URL that explains how to make the data inserts without any real pain.
The 3rd section is a 3rd way of solving this particular problem. Notice that it's the classic "Find the nth occurance" in that if you change the WHERE MyRank = 1 to WHERE MYRank = 2... it will find the rev just before the last one for each SN. You can even say "Find the last two revs" by changing it to WHERE MyRank BETWEEN 1 and 2. Here's the code...
[font="Courier New"]--===== Create a demo table
DECLARE @yourTable TABLE
        (
        UniqueID       INT PRIMARY KEY CLUSTERED,
        SN             VARCHAR(10),
        RevisionNumber INT
        )
--===== Populate the demo table with data
 INSERT INTO @yourTable
        (UniqueID,SN,RevisionNumber)
 SELECT 1,'GM01',0 UNION ALL
 SELECT 2,'GM02',0 UNION ALL
 SELECT 3,'GM02',1 UNION ALL
 SELECT 4,'GM01',1 UNION ALL
 SELECT 5,'GM02',2 UNION ALL
 SELECT 6,'GM02',3
--===== Demo a third solution (CTE can be changed to derived table)
 WITH cteRankedData AS
(
 SELECT ROW_NUMBER() OVER (PARTITION BY SN ORDER BY SN, RevisionNumber DESC) AS MyRank,
        UniqueID,SN,RevisionNumber
   FROM @yourTable
)
 SELECT UniqueID,SN,RevisionNumber
   FROM cteRankedData
  WHERE MyRank = 1  --<<<&change to solve other "Nth row" problems[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2008 at 3:14 pm
Jeff, I didn't mean to step on your toes and indicate that either of these scripts were different. I was speaking in general terms to let our Newbie know that no matter what solution to always verify that it not only returns the correct data but also that the solution was acceptable in terms of perfomance, etc. I know I've run into situations where I've been presented a rather elegant solution by a developer only to see table and or index scans, or, in one bad case, where a query returned over million rows from a linked server before applying a filter on the local server.
It all boils down to CYA...
DAB
March 5, 2008 at 3:28 pm
Oh no... not a problem Doug... I didn't even see your post until I posted that last one. Nope, when I said "Well then, let's show the new guy how to do it right", I was actually referring to what the "new guy" said. Out of context, it does look a bit like I was ragging on you and that's just not the case. Your post got wedged in between the one I was referring to and mine. I should have used the quote (in fact, I edited the post so it would have it).
Thanks for your feedback, Doug... but you were not out of line at all and I took zero offense. In fact, thanks for bringing up the thing about performance... I sure am sorry about the confusion...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 6:44 am
Thanks again everyone for your quick replies and raising issues around criteria for choosing the best among multiple solutions. I'll try to keep your suggestions in mind as I wade deeper into SQL. Appreciate all the help!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply