none
Select statement to obtain a value based on the max value of another field

    Question

  • Not sure if I'm in the correct forum, but I'm trying to devise a query that will return me the approprate data for the maximum values base on the sum of these values. 

    For example, I need to return the following data in MyTable 

    AccountID, ItemTypeID, IntendedUseID, OrderQty
    2327, 2, 1, 1300
    2327, 1, 2, 839
    2327, 1, 3, 2006
    2327, 1, 2, 1254
    2327, 1, 2, 2177
    4765, 1, 2, 1254
    4765, 1, 4, 3000
    4765, 1, 4, 1000
    4765, 2, 2, 156
    
    

    into the following result set.

    AccountID, ItemTypeID, IntendedUseID, OrderQty
    2327, 2, 1, 1300
    2327, 1, 2, 4270
    4765, 1, 4, 4000
    4765, 2, 2, 156

    The key here is that I require AccountID, ItemTypeID,  IntendedUseID, and Sum(OrderQty) based on the grouping of  AccountID, ItemTypeID and IntendedUseID, but I only want the records based on AccountID, ItemTypeID,  IntendedUseID, and Max( Sum(OrderQty) ).

    Hope this makes sence, and can I do this in one statement?

    Regards,

    Andrew

     

    Monday, October 18, 2010 2:59 PM

Answers

  • Hi Andrew,

    Which version of SQL Server are you using? If it is SQL Server 2005 or later, we can use common table expressions and the ROW_NUMBER to get your expected result.

    Please see:

    CREATE TABLE #MyTable (AccountID INT,ItemTypeID INT,IntendedUseID INT,OrderQty INT)
    
    INSERT INTO #MyTable VALUES (2327,2,1,1300)
    INSERT INTO #MyTable VALUES (2327,1,2,839)
    INSERT INTO #MyTable VALUES (2327,1,3,2006)
    INSERT INTO #MyTable VALUES (2327,1,2,1254)
    INSERT INTO #MyTable VALUES (2327,1,2,2177)
    INSERT INTO #MyTable VALUES (4765,1,2,1254)
    INSERT INTO #MyTable VALUES (4765,1,4,3000)
    INSERT INTO #MyTable VALUES (4765,1,4,1000)
    INSERT INTO #MyTable VALUES (4765,2,2,156)
    
    ;WITH cte1
    AS
    (
    	SELECT AccountID,ItemTypeID,IntendedUseID,SUM(OrderQty) AS SumOrderQty 
    	FROM #MyTable
    	GROUP BY AccountID,ItemTypeID,IntendedUseID
    ),
    cte2
    AS
    (
    	SELECT AccountID,ItemTypeID,IntendedUseID,SumOrderQty,
    	ROW_NUMBER() OVER (PARTITION BY AccountID,ItemTypeID ORDER BY SumOrderQty DESC) AS 'RowNum'
    	FROM cte1
    )
    
    SELECT AccountID,ItemTypeID,IntendedUseID,SumOrderQty 
    FROM cte2 
    WHERE RowNum = 1
    

    We can create a stored procedure using the statements above.

    References:

    Using Common Table Expressions
    http://msdn.microsoft.com/en-us/library/ms190766.aspx

    ROW_NUMBER (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms186734.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by wattsy01 Tuesday, October 19, 2010 8:01 AM
    Tuesday, October 19, 2010 2:56 AM

All replies

  • Hi Andrew,

    Which version of SQL Server are you using? If it is SQL Server 2005 or later, we can use common table expressions and the ROW_NUMBER to get your expected result.

    Please see:

    CREATE TABLE #MyTable (AccountID INT,ItemTypeID INT,IntendedUseID INT,OrderQty INT)
    
    INSERT INTO #MyTable VALUES (2327,2,1,1300)
    INSERT INTO #MyTable VALUES (2327,1,2,839)
    INSERT INTO #MyTable VALUES (2327,1,3,2006)
    INSERT INTO #MyTable VALUES (2327,1,2,1254)
    INSERT INTO #MyTable VALUES (2327,1,2,2177)
    INSERT INTO #MyTable VALUES (4765,1,2,1254)
    INSERT INTO #MyTable VALUES (4765,1,4,3000)
    INSERT INTO #MyTable VALUES (4765,1,4,1000)
    INSERT INTO #MyTable VALUES (4765,2,2,156)
    
    ;WITH cte1
    AS
    (
    	SELECT AccountID,ItemTypeID,IntendedUseID,SUM(OrderQty) AS SumOrderQty 
    	FROM #MyTable
    	GROUP BY AccountID,ItemTypeID,IntendedUseID
    ),
    cte2
    AS
    (
    	SELECT AccountID,ItemTypeID,IntendedUseID,SumOrderQty,
    	ROW_NUMBER() OVER (PARTITION BY AccountID,ItemTypeID ORDER BY SumOrderQty DESC) AS 'RowNum'
    	FROM cte1
    )
    
    SELECT AccountID,ItemTypeID,IntendedUseID,SumOrderQty 
    FROM cte2 
    WHERE RowNum = 1
    

    We can create a stored procedure using the statements above.

    References:

    Using Common Table Expressions
    http://msdn.microsoft.com/en-us/library/ms190766.aspx

    ROW_NUMBER (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms186734.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by wattsy01 Tuesday, October 19, 2010 8:01 AM
    Tuesday, October 19, 2010 2:56 AM
  • Many thanks,

    Andrew

    Tuesday, October 19, 2010 8:01 AM