Post reply

Group by where MAX can be the same

  • February 28, 2008 at 10:04 am

    #74636

    Having a 'I know I know this..." kind of day

    Got a test table

    -- Test data

    -- drop table #t1

    create table #t1

    (

    h_id int, -- Header Id

    l_id int, -- Line Id

    ton decimal(9,4) --column containing MAX Value we're after

    )

    insert #t1 values (1,1,5)

    insert #t1 values (1,2,6)

    insert #t1 values (1,3,4)

    insert #t1 values (2,1,2)

    insert #t1 values (3,1,10)

    insert #t1 values (3,2,12)

    insert #t1 values (3,3,12)

    -- Which

    select h_id, l_id, ton

    from #t1 X

    -- Results

    h_id l_id ton

    ----------- ----------- -----------

    1 1 5.0000

    1 2 6.0000

    1 3 4.0000

    2 1 2.0000

    3 1 10.0000

    3 2 12.0000

    3 3 12.0000

    -- desired Result, the line with the max ton

    h_id l_id ton

    ----------- ----------- -----------

    1 2 6.0000

    2 1 2.0000

    3 3 12.0000

    Note that the l_id for h_id 3 could be 2 in the example above, and the developer says he doesn't care which line he gets as long as it's consistent. So I would argue the Max l_id with the Max Ton then.

    Got so far:

    select h_id, l_id, max(maxTon) as maxTon

    from

    (

    select t1.h_id, max(t1.l_id) l_id , max(t1.ton) maxTon

    FROM #t1 t1

    group by t1.h_id --, t1.l_id

    ) y

    group by h_id , l_id

    which returns

    h_id l_id maxTon

    ----------- ----------- ----------------------------------------

    1 3 6.0000

    2 1 2.0000

    3 3 12.0000

    but this is wrong. Arrgh! Why can't I remember how to do this? Nurse! Pass the coffee... 🙂

    Any help much appreciated.

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • February 28, 2008 at 10:15 am

    #783346

    Using a different approach

    select X.h_id, X.l_id, X.ton

    from #t1 X

    where not exists (select * from #t1 Y

    where Y.h_id=X.h_id

    and (Y.ton>X.ton or (Y.ton=X.ton and X.l_id<Y.l_id)))

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 28, 2008 at 10:16 am

    #783348

    Kev (the developer) has come up with this

    select h_id, max(l_id), max(ton)

    from #t1 X

    where ton =

    (

    SELECT MAX(ton)

    from #t1 Y

    WHERE Y.h_id = X.h_id

    )

    group by h_id

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • February 28, 2008 at 10:46 am

    #783359

    Mark (2/28/2008)

    Using a different approach

    select X.h_id, X.l_id, X.ton

    from #t1 X

    where not exists (select * from #t1 Y

    where Y.h_id=X.h_id

    and (Y.ton>X.ton or (Y.ton=X.ton and X.l_id<Y.l_id)))

    Thanks Mark.

    my head hurts...

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • February 29, 2008 at 8:39 am

    #783827

    Hi David

    I would use something like this 'cos it's quick...

    SELECT X.h_id, MAX(X.l_id), X.ton

    FROM #t1 X

    INNER JOIN (SELECT h_id, MAX(ton) AS MAXton FROM #t1 GROUP BY h_id) dt

    ON dt.h_id = X.h_id AND dt.MAXton = X.ton

    GROUP BY X.h_id, X.ton

    ...and it's probably how you would have done it on a more normal and less pressurised day!

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 29, 2008 at 8:49 am

    #783841

    Yes Chris, that's exactly what I was chasing down. 🙂

    I mean I knew you could do this without a correlated sub-query, but I was struggling to prove it... :w00t:

    Dave J

    PS tried to use the Strike mark up so people could see what I had edited, but it seems to have stopped working?

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • February 29, 2008 at 9:35 am

    #783908

    David Jackson (2/29/2008)

    Yes Chris, that's exactly what I was chasing down. 🙂

    I mean I knew you could do this without a correlated sub-query, but I was struggling to prove it... :w00t:

    Dave J

    PS tried to use the Strike mark up so people could see what I had edited, but it seems to have stopped working?

    You are having a bad day - but hey, it's beertime in an hour!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply