Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,
);
share|improve this question
1  
Is there some nuance to your question that prevents you from using MAX thus SELECT T.PrntP_PstnGrpID, MAX(T.ChldLevel) FROM dbo.MyTable T WHERE T.PrntP_PstnGrpID = 10 GROUP BY PrntP_PstnGrpID) If you need the actual row, then use ROW_NUBMER to segment your set –  billinkc Oct 3 '13 at 18:11
    
Because MAX requires the use of Grouping and when I do SELECT MAX(ChldLevel) AS 'ChildLevel', SORT, PrntP_PstnGrpID FROM dbo.P_PositionGroupFlat GROUP BY SORT, PrntP_PstnGrpID ORDER BY PrntP_PstnGrpID; I am still getting duplicates –  user11512 Oct 3 '13 at 18:24
1  
No need to get bent out of shape. No one stated your question was fecal level. Your stated question morphed from describing one problem to a similar but different one. –  billinkc Oct 3 '13 at 19:04
5  
Do ask questions. Gain a sh*t shield and learn to plow through flak and get better at what you do. Doesn't matter what community you are attached to. Focus on your end result (solving your problem and getting better) and ignore the slings and arrows along the way –  billinkc Oct 3 '13 at 19:07
3  
@MatthewVerstraete I think you have misunderstood. Noone said it was a silly question, only not clear enough (now it is I think, you can correct it if you think that is not what you want.) And when billinc mentioned (in the comments of my deleted answer) "lame reference", he was talking about his own previous joke about "rows vs. records". He was not referring to your question. –  ypercube Oct 4 '13 at 14:26

1 Answer 1

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

share|improve this answer
    
Also with: MAX(P.ChldLevel) OVER (PARTITION BY P.PrntP_PstnGrpID) AS MaxChldLevel and then WHERE ChldLevel = MaxChldLevel –  ypercube Oct 3 '13 at 18:50
    
Does that work with 2005? I thought the enhancements to the OVER clause was a 2008+ thing. –  billinkc Oct 3 '13 at 19:01
3  
Msdn says it works: OVER clause (2005 version) –  ypercube Oct 3 '13 at 19:05
    
@billinkc: Their first take on the OVER clause was with the 2005 release. They didn't enhance it until the 2012 version. –  Andriy M Oct 14 '13 at 15:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.