Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ArnoldG
Starting Member
36 Posts |
 Posted - 2013-10-23 : 08:56:55
|
| Hello,Can someone help me on how to filter on a maximum value ?My query is this:SELECT ProdspecUID ,itemcode,model,revno FROM prodspecThe revision numbers (revno) are unique per itemcode.I would like to get only the rows with the highest revision per itemcode, but I also need the prodspecUID with is the index key and therefore unique.I have tried MAX() and TOP 1's in sub queries, but up until now without any result.How should I solve this ?Thx !Arnold |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-23 : 09:10:25
|
| [code]SELECT ProdspecUID ,itemcode,model,revnoFROM(SELECTProdspecUID ,itemcode,model,revno,ROW_NUMBER() OVER (PARTITION BY itemcode ORDER BY revno DESC) AS RNFROM prodspec)tWHERE RN = 1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
ArnoldG
Starting Member
36 Posts |
Posted - 2013-10-23 : 09:52:55
|
| Work perfectly !I could not have figured that out myself...Thanks a lot,Arnold |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-23 : 13:07:51
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
T-SQL Filter on maximum value