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

Question

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

justy

Old Hand

Points: 371
More actions
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.

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

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

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 !!!**

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 2

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

justy Old Hand Points: 371 More actions · Answer 3

That's exactly what I wanted - thanks!

I enjoyed the "urn" joke!

David Burrows SSC Guru Points: 65142 More actions · Answer 4

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.

justy Old Hand Points: 371 More actions · Answer 5

justy

Old Hand

Points: 371

December 16, 2005 at 8:14 am

#610073

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