asked on

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

Dulton

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

Sharath S

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

urthrilled

ASKER

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!

PortletPaul

>>"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