top count row

Question

top count row

neliii

Say Hey Kid

Points: 701
More actions
June 2, 2010 at 10:31 pm

#387074

create table db1
(
count int,
name varchar (200),
state varchar (100),
age int,
gender varchar(100)
)
insert into db1
values(1,'sun','ca',21,'m')
insert into db1
values(2,'sun','wa',21,'f')
insert into db1
values(3,'sun','tx',21,'m')
insert into db1
values(3,'bun','oh',21,'m')
insert into db1
values(4,'bun','il',21,'f')
insert into db1
values(1,'gun','al',21,'m')
insert into db1
values(8,'gun','or',21,'f')
insert into db1
values(7,'pen','dc',21,'f')
insert into db1
values(4,'pen','ac',21,'m')
insert into db1
values(1,'pen','la',21,'f')
insert into db1
values(3,'kin','ab',21,'f')
insert into db1
values(2,'kin','ga',21,'f')
count name state age gender
1 sun ca 21 m
2 sun wa 21 f
3 sun tx 21 m
3 bun oh 21 m
4 bun il 21 f
1 gun al 21 m
8 gun or 21 f
7 pen dc 21 f
4 pen ac 21 m
1 pen la 21 f
3 kin ab 21 f
2 kin ga 21 f
so now in the above table i want to get the records based on names with the top count
for example when we take first name sun its top count is 3 so i want the complete row like
3 sun tx 21 m
and also the same for others
so the output will be like
3 sun tx 21 m
4 bun il 21 f
8 gun or 21 f
7 pen dc 21 f
3 kin ab 21 f

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

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Am I completely crazy or did you ask pretty much the exact same question in this thread, which is complete with an answer that went completely unrecognized?

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 2

bteraberry (6/2/2010)
Am I completely crazy or did you ask pretty much the exact same question in this thread, which is complete with an answer that went completely unrecognized?

Certainly looks that way.

Depressing.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

Paul White NZ (6/3/2010)
bteraberry (6/2/2010)
Am I completely crazy or did you ask pretty much the exact same question in this thread, which is complete with an answer that went completely unrecognized?
Certainly looks that way.
Depressing.

Is it possible to lock down this thread then?

Thomas Rushton
blog: https://thelonedba.wordpress.com

Paul White SSC Guru Points: 150467 More actions · Answer 4

Sadly...no

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi