[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

SQL Statement getting MAX without including all the items in the group by clause

I need the latest record (MAX(Rev_No), but need assistance with the select statement to include all the fields I need to see, but not group by them
This statement runs, however returns every record where the description was changed because it's in the group by clause.

How do I get all the fields I need - only from the row with the maximum Rev_No?

      SELECT Project_ID,
                  Item_No,
                  Item_Series,
                  Item_Sub_No,
                  Item_Description,
                  BarSeam,
                  BarStrike,
                  BarFinish,
                  BarCut,
                  BarShop,
                  BarProto
      FROM ICS_Interior_Trans_Master
      WHERE
            Project_ID=4  
      group by
       Project_ID,
                  Item_No,
                  Item_Series,
                  Item_Sub_No,
                  Item_Description,
                  BarSeam,
                  BarStrike,
                  BarFinish,
                  BarCut,
                  BarShop,
                  BarProto
having
exists((Select Project_ID,Item_Series,Item_No, Item_Sub_No,max(Rev_No) as Rev_No from ICS_Interior_Trans_Master group by Project_ID,Item_Series,Item_No, Item_Sub_No))


Thank you
0
urthrilled
Asked:
urthrilled
1 Solution
 
DultonCommented:
2 steps... you need to use something like ROW_NUMBER or RANK or DENSERANK to get the items partitioned and ordered correctly.... after you order them, select where the order = 1..

;With OrderRecs AS
(
      SELECT Project_ID,
                  Item_No,
                  Item_Series,
                  Item_Sub_No,
                  Item_Description,
                  BarSeam,
                  BarStrike,
                  BarFinish,
                  BarCut,
                  BarShop,
                  BarProto,
                  ROW_NUMBER() (OVER PARTITION BY Project_ID,Item_Series,Item_No, Item_Sub_No ORDER BY Rev_No DESC) AS [RowOrder]
      FROM ICS_Interior_Trans_Master 
      WHERE
            Project_ID=4  
)

SELECT * FROM OrderRecs WHERE RowOrder = 1

Open in new window

0
 
SharathData EngineerCommented:
SELECT Project_ID,
                  Item_No,
                  Item_Series,
                  Item_Sub_No,
                  Item_Description,
                  BarSeam,
                  BarStrike,
                  BarFinish,
                  BarCut,
                  BarShop,
                  BarProto
FROM (				  
SELECT Project_ID,
                  Item_No,
                  Item_Series,
                  Item_Sub_No,
                  Item_Description,
                  BarSeam,
                  BarStrike,
                  BarFinish,
                  BarCut,
                  BarShop,
                  BarProto,
ROW_NUMBER() OVER(PARTITION BY Project_ID,Item_Series,Item_No, Item_Sub_No ORDER BY Rev_No DESC) rn
      FROM ICS_Interior_Trans_Master 
      WHERE
            Project_ID=4) T1 WHERE rn = 1 

Open in new window

0
 
SharathData EngineerCommented:
SELECT Project_ID, 
       Item_No, 
       Item_Series, 
       Item_Sub_No, 
       Item_Description, 
       BarSeam, 
       BarStrike, 
       BarFinish, 
       BarCut, 
       BarShop, 
       BarProto 
  FROM ICS_Interior_Trans_Master t1 
 WHERE Project_ID = 4 
   AND Rev_No = (SELECT MAX(Rev_No) 
                   FROM ICS_Interior_Trans_Master t2 
                  GROUP BY Project_ID, 
                           Item_Series, 
                           Item_No, 
                           Item_Sub_No) 

Open in new window

0
 
urthrilledAuthor Commented:
Works perfectly, I've never used partition and row number together before and it seems more complicated than just choosing the row with the maximum revision number, but I'm grateful, Thank you!
0
 
PortletPaulCommented:
>>"never used partition and row number together before and it seems more complicated than ..."

functions like row_umber are exceptionally handy and very efficient

It will pay off handsomely if you familiarize your self with these e.g.
http://technet.microsoft.com/en-us/library/ms186734.aspx

also see "over"
http://technet.microsoft.com/en-us/library/ms189461.aspx
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now