Select Top 3 Names - SQL Server Forums

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

SQL Server Development (2000)

Select Top 3 Names

Author

Topic

prakashdotc
Starting Member

25 Posts

Posted - 2007-05-07 : 05:13:08

Hi every one,

here i have a problem, a table which has names starts form a to z. i need a query to get top 3 name in a single row as

Name1 = Top 1 Name
Name2 = Top 2 Name and
Name3 = Top 3 Name.

Table has columns as Name and Gender

thanks in advance

-Prakash.C

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 05:20:46

In single row? in which format? CSV?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 05:25:24

declare @tt table(col1 varchar(20))
insert @tt
select 'arau' union all
select 'vijay' union all
select 'arau' union all
select 'raja' union all
select 'pasu' union all
select 'baskar'

Select
Max(case when t.num =5 then col1 end) as Top1,
Max(case when t.num =4 then col1 end) as Top2,
Max(case when t.num =3 then col1 end) as Top3 from
(select col1,
num = (select count(distinct(col1))
from @tt where col1 >= a.col1)
from @tt a) t

prakashdotc
Starting Member

25 Posts

Posted - 2007-05-07 : 05:37:42

Hi pbguy, thank u for U'r reply i tried as if u said, the query and result as shown below

select max(case when t.num = 5 then BabyName end) as top1, max(case when t.num = 4 then BabyName end) as top2,
max(case when t.num = 3 then BabyName end) as top3 from (select BabyName, num = (select count(BabyName)
from BabyNames where BabyName >= a.BabyName) from BabyNames a) t

the result of the query is as follows

vasdf, wasdf, xasdf

but i need from top as

Aabike, Abckia, Actria.

can u help me further in this problem

thanks

- Prakash.C

prakashdotc
Starting Member

25 Posts

Posted - 2007-05-07 : 05:42:30

sorry pbguy, u r correct, made some changes in query and it worked fine thank u very much

- Prakash.C

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 05:42:45

sorry man....small mistake...

Select
Max(case when t.num =1 then col1 end) as Top1,
Max(case when t.num =2 then col1 end) as Top2,
Max(case when t.num =3 then col1 end) as Top3 from
(select col1,
num = (select count(distinct(col1))
from @tt where col1 <= a.col1)
from @tt a) t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-07 : 08:29:31

quote:
Originally posted by prakashdotc

Hi every one,

here i have a problem, a table which has names starts form a to z. i need a query to get top 3 name in a single row as

Name1 = Top 1 Name
Name2 = Top 2 Name and
Name3 = Top 3 Name.

Table has columns as Name and Gender

thanks in advance

-Prakash.C

Where do you want to show data?
The above suggested query will be ineffecient if the table has thousands of rows

What you need is

Select distinct Name from table
order by Name

And when displaying them pivot them

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources