Sql-server – Selecting the highest value in a column

sql-server-2005t-sql

I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple entries but each entry will have an ever increasing ChldLevel starting at 1) How can I write a select statement to pull the row with the highest ChldLevel value? (I.E If PrntP_PstnGrpID = 10 has 3 entries of ChldLevel 1,2,3 I want to get the row with ChldLevel 3 but if PrntP_PstnGrpID = 5 has 5 entries of ChldLevel 1,2,3,4,5 I get the row with ChldLevel 5)

CREATE TABLE [dbo].[P_PositionGroupFlat] (
    [ID]                      INT            NOT NULL,
    [PrntP_PstnGrpID]         INT            NOT NULL,
    [ChldLevel]               INT            NOT NULL,
    [Sort]                    VARCHAR (8000) NOT NULL,
);

Best Answer

Beyond ypercube's answer, I would solve this with either of the following queries. I like the first because it's easier for my poor head to comprehend.

The second usually provides better performance but it always takes me a bit to "get it." 

-- this approach uses row_number to provide an ordered window based
-- resetting whenever the group id changes
SELECT
*
FROM
(
    SELECT
        P.*
    ,   ROW_NUMBER() OVER (PARTITION BY P.PrntP_PstnGrpID ORDER BY P.ChldLevel DESC) AS rn
    FROM
        dbo.P_PositionGroupFlat P
) D
WHERE D.rn = 1
ORDER BY 1;


-- this approach is my new favorite
-- has shown better performance than the above approach
SELECT
P.*
FROM
    dbo.P_PositionGroupFlat P
WHERE
    P.ChldLevel = 
    (
        SELECT
            MAX(PI.ChldLevel)
        FROM
            dbo.P_PositionGroupFlat PI
        WHERE
            PI.PrntP_PstnGrpID = P.PrntP_PstnGrpID
    )
ORDER BY 1;

http://sqlfiddle.com/#!3/3a958/4

Related Question