How to get distinct subgroup of data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to get distinct subgroup of data?

Author

Topic

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2010-07-08 : 17:26:07

Hi,

I have the a table with the following structure:

CREATE TABLE test(aid, bid, score);

INSERT INTO test(aid, bid, score) VALUES(1,3,100);
INSERT INTO test(aid, bid, score) VALUES(2,3,80);
INSERT INTO test(aid, bid, score) VALUES(4,3,80);
INSERT INTO test(aid, bid, score) VALUES(6,5,15);
INSERT INTO test(aid, bid, score) VALUES(8,5,80);

For each 'bid', we have multiple 'aid' with a different score. I'd like a query which will return
the following from test table:

1,3,100
8,5,80

That is, return only 1 row per distinct 'bid'. That row has to contain the highest score.
Note that scores could be the same within a group, in which case either row is fine as an output.

How can I do that?

Thanks a lot!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-08 : 23:50:01

Try this:

Select * from Test t1 where score = (Select max(score) from test T2
Where t2.bid = t1.bid)

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2010-07-09 : 01:07:27

[code]
try also this..

CREATE TABLE #test(aid int, bid int, score int);

INSERT INTO #test(aid, bid, score) VALUES(1,3,100);
INSERT INTO #test(aid, bid, score) VALUES(2,3,80);
INSERT INTO #test(aid, bid, score) VALUES(4,3,80);
INSERT INTO #test(aid, bid, score) VALUES(6,5,15);
INSERT INTO #test(aid, bid, score) VALUES(8,5,80);

select t.*
from #test t
inner join (
select tt.bid, max(tt.score) score
from #test tt
group by tt.bid
) r on r.bid = t.bid
and r.score = t.score

drop table #test

[/code]

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2010-07-09 : 15:58:02

Thanks for the suggestions!

whitesword
Starting Member

17 Posts

Posted - 2010-07-19 : 03:17:39

Continuing on from this I need an additional wrinkle added where I only want the 1st line from the above test data where there are more than 1 bid of the same value.

create table Test ( aid int, bid int, score int )

insert into Test (aid,bid,score) values ( 1, 3, 100 )
insert into Test (aid,bid,score) values ( 2, 3, 80 )
insert into Test (aid,bid,score) values ( 4, 3, 80 )
insert into Test (aid,bid,score) values ( 6, 5, 15 )
insert into Test (aid,bid,score) values ( 8, 5, 80 )
insert into Test (aid,bid,score) values ( 9, 3, 100 )

I would like it to return

1, 3, 100
8, 5, 80

being as aid 1 want placed 1st

The above query returns aid 1 and 9 as well and I do NOT want the last 9, 3, 100 returned

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-19 : 07:56:53

[code]select aid = min(aid),
bid, score
from
(
select t.*
from test t
inner join
(
select bid, score = max(score)
from test
group by bid
) m on t.bid = m.bid
and t.score = m.score
) t
group by bid,score[/code]

KH
[spoiler]Time is always against us[/spoiler]

whitesword
Starting Member

17 Posts

Posted - 2010-07-23 : 03:59:09

*facepalm* Now why didn't I think of that. Thank you very much for that :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources