Post reply

Problem selecting records of a type in a group using a max()

  • December 16, 2005 at 6:13 am

    #90175

    Hi,

    I have a table variable of records. Each record has a type, a group id and a urn. I need to select one record with the largest urn from each group for a particular type.

    I can write a function to do this using a loop, but can I do this in one select statement?

    For example: I need to select one record per group for type = 'A'.

    declare @x table ([id] int identity (1,1), [type] varchar(8), [grouping_id] int, [urn] int)
    insert into @x ([type], [grouping_id], [urn]) values ('A', 1, 1)

    insert into @x ([type], [grouping_id], [urn]) values ('B', 1, 2)

    insert into @x ([type], [grouping_id], [urn]) values ('B', 2, 3)

    insert into @x ([type], [grouping_id], [urn]) values ('A', 3, 4)

    insert into @x ([type], [grouping_id], [urn]) values ('A', 3, 5)

    The results shoud be:

    id      type      grouping_id urn
    ------ --------- ------------- ---
    1       A          1               1
    5       A          3               5

    As you can see by the results, group 3 has two records of type A, but only the record 5 with the largest urn has been selected. Also, no records of type B have been selected even though record 2 is in a group with a type A record.

    Any help would be much appreciated, thankyou.

     

  • December 16, 2005 at 6:53 am

    #610035

    This what you want..?!

    select max(id) as id, max(type) as type, grouping_id, max(urn) as urn
from @x where type = 'A' group by grouping_id

    ps:what's an "urn" - is it the same as the "grecian" one ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • December 16, 2005 at 7:24 am

    #610048

    Sushila's method may link the wrong id to the wrong data, unless data are always sorted by grouping_id, urn when they are inserted into the table. Consider the following data (I have interchanged two rows, such that id 4 and 5 are switched):

    declare @x table ([id] int identity (1,1), [type] varchar(8), [grouping_id] int, [urn] int)

    insert into @x ([type], [grouping_id], [urn]) values ('A', 1, 1)

    insert into @x ([type], [grouping_id], [urn]) values ('B', 1, 2)

    insert into @x ([type], [grouping_id], [urn]) values ('B', 2, 3)

    insert into @x ([type], [grouping_id], [urn]) values ('A', 3, 5)

    insert into @x ([type], [grouping_id], [urn]) values ('A', 3, 4)

    Sushila's method returns

    1           A        1           1

    5           A        3           5

    while my method returns

    1           A        1           1

    4           A        3           5

     

    My method is as follows (edited, I put in the wrong sql at first):

    select max(x.id), x.grouping_id, x.urn from @x x inner join

    (

    select grouping_id, max(urn) as urn from @x where type = 'A' group by grouping_id

    )

    y

    on x.grouping_id = y.grouping_id and x.urn = y.urn

    group by x.grouping_id, x.urn

    If the combination of type, grouping_id and urn is unique, this can be simplified:

    select x.* from @x x inner join

    (

    select grouping_id, max(urn) as urn from @x where type = 'A' group by grouping_id

    )

    y

    on x.grouping_id = y.grouping_id and x.urn = y.urn

  • December 16, 2005 at 7:26 am

    #610049

    That's exactly what I wanted - thanks!

    I enjoyed the "urn" joke!

     

  • December 16, 2005 at 7:42 am

    #610055

    quote I enjoyed the "urn" joke!

    and what's a "grecian" "urn"

    a damn sight more than me

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 16, 2005 at 8:14 am

    #610073

    Thanks Jesper, I didn't even spot that one!

     

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

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