Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-04-09
5
Medium Priority
?
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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 41

Accepted Solution

by:
Sharath earned 2000 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 49

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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