Solved

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

Posted on 2014-04-09
5
279 Views
Last Modified: 2014-04-09
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
Comment
Question by:urthrilled
5 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 39990017
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39990021
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39990029
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
 
LVL 4

Author Comment

by:urthrilled
ID: 39990068
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39990548
>>"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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
t-sql month question 8 42
Optimize/Performance Tune Production Databases 11 40
Updating variable table 9 17
Slow SQL query 12 17
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now