By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,696 Members | 1,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,696 IT Pros & Developers. It's quick & easy.

top 2

P: n/a
#Table(class, name,exam,score)
A name1 math 100
A name1 math 88
A name1 Phy 98
A name1 Chm 98
A name1 SPT 89
A name1 math 23
A name1 math 45
A name2 math 54
A name2 math 79
A name2 Phy 79
A name2 Chm 44
A name2 SPT 34
A name2 math 45
A name2 math 65
B name1 math 54
B name1 math 23
B name1 Phy 54
B name1 Chm 98
B name1 SPT 89
B name1 math 48
B name1 math 63
B name2 math 35
B name2 math 35
B name2 Phy 33
B name2 Chm 66
B name2 SPT 12
B name2 math 23
B name2 math 55

need to display all columns from #table
+ top 2 math scores
grouped by class and name.

drop table #temp

output Like:

A name1 math 100
A name1 math 88
A name1 Phy 98
A name1 Chm 98
A name1 SPT 89
A name1 math 23
A name1 math 45
A name2 math 54
A name2 math 79
A name2 Phy 79
A name2 Chm 44
A name2 SPT 34
A name2 math 45
A name2 math 65
B name1 math 54
B name1 math 23
B name1 Phy 54
B name1 Chm 98
B name1 SPT 89
B name1 math 48
B name1 math 63
B name2 math 35
B name2 math 35
B name2 Phy 33
B name2 Chm 66
B name2 SPT 12
B name2 math 23
B name2 math 55
100
88

79
65

63
54

55
35


--
Sent by 3 from yahoo within field com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Alexqa,

See following example.
--Sample table and records.
create table #Table(class char(1), name varchar(20),exam varchar(20),score
int)
insert into #table
select 'A' ,'name1', 'math' ,100
union all select 'A' ,'name1', 'math' ,88
union all select 'A' ,'name1', 'phy' ,98
union all select 'A' ,'name1', 'chm' ,98
union all select 'A' ,'name1', 'spt' ,89
union all select 'A' ,'name1', 'math' ,23
union all select 'A' ,'name1', 'math' ,45
union all select 'A' ,'name2', 'math' ,54
union all select 'A' ,'name2', 'math' ,79
union all select 'A' ,'name2', 'phy' ,79
union all select 'A' ,'name2', 'chm' ,44
union all select 'A' ,'name2', 'spt' ,34
union all select 'A' ,'name2', 'math' ,45
union all select 'A' ,'name2', 'math' ,65
union all select 'B','name1', 'math' ,54
union all select 'B','name1', 'math' ,23
union all select 'B','name1', 'phy' ,54
union all select 'B','name1', 'chm' ,98
union all select 'B','name1', 'spt' ,89
union all select 'B','name1', 'math' ,48
union all select 'B','name1', 'math' ,63
union all select 'B','name2', 'math' ,35
union all select 'B','name2', 'math' ,35
union all select 'B','name2', 'phy' ,33
union all select 'B','name2', 'chm' ,66
union all select 'B','name2', 'spt' ,12
union all select 'B','name2', 'math' ,23
union all select 'B','name2', 'math' ,55

--Required query

select * from
(select a.class,a.name,a.score,
(select count(distinct score) from #table b
where a.class = b.class and a.name = b.name
and a.score <= b.score and a.exam = b.exam) rank
from #table a where a.exam ='math') X
where rank <= 2
order by class,name,score desc

--
-Vishal
Jul 20 '05 #2

P: n/a
Dear Vishal Parkar,

In your posting Re: top 2 from Sun, 19 Oct 2003 12:17:53 +0530 you
write:

Alexqa,

See following example.
--Sample table and records.
create table #Table(class char(1), name varchar(20),exam varchar(20),score
int)
insert into #table
select 'A' ,'name1', 'math' ,100
union all select 'A' ,'name1', 'math' ,88
union all select 'A' ,'name1', 'phy' ,98
union all select 'A' ,'name1', 'chm' ,98
union all select 'A' ,'name1', 'spt' ,89
union all select 'A' ,'name1', 'math' ,23
union all select 'A' ,'name1', 'math' ,45
union all select 'A' ,'name2', 'math' ,54
union all select 'A' ,'name2', 'math' ,79
union all select 'A' ,'name2', 'phy' ,79
union all select 'A' ,'name2', 'chm' ,44
union all select 'A' ,'name2', 'spt' ,34
union all select 'A' ,'name2', 'math' ,45
union all select 'A' ,'name2', 'math' ,65
union all select 'B','name1', 'math' ,54
union all select 'B','name1', 'math' ,23
union all select 'B','name1', 'phy' ,54
union all select 'B','name1', 'chm' ,98
union all select 'B','name1', 'spt' ,89
union all select 'B','name1', 'math' ,48
union all select 'B','name1', 'math' ,63
union all select 'B','name2', 'math' ,35
union all select 'B','name2', 'math' ,35
union all select 'B','name2', 'phy' ,33
union all select 'B','name2', 'chm' ,66
union all select 'B','name2', 'spt' ,12
union all select 'B','name2', 'math' ,23
union all select 'B','name2', 'math' ,55

--Required query

select * from
(select a.class,a.name,a.score,
(select count(distinct score) from #table b
where a.class = b.class and a.name = b.name
and a.score <= b.score and a.exam = b.exam) rank
from #table a where a.exam ='math') X
where rank <= 2
order by class,name,score desc

--
-Vishal

this returns all columns + rank not top 2 values.



--
Spam protected message from:
Sent by 3 from yahoo subpart from com
Posted via http://www.usenet-replayer.com
Jul 20 '05 #3

P: n/a
Alexqa,

Can you post what is your expected output, I think from your post you want to retrieve following
values/or its respective rows.

100
88
79
65
63
54
55
35
If that is the case then following query will return the same. If you want to return only score
column, include only score column in first SELECT. The following query will return you the distinct
values for class,name and score.

select distinct class,name,score from
(select a.class,a.name,a.score,
(select count(distinct score) from #table b
where a.class = b.class and a.name = b.name
and a.score <= b.score and a.exam = b.exam) rank
from #table a where a.exam ='math') X
where rank <= 2
order by class,name,score desc

If this is not what you want post the required output that you are looking for.

--
- Vishal

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.