Help needed for writing a Set Based query for finding the highest marks obtained by the students

Posted by priyanka.sarkar_2 on Stack Overflow See other posts from Stack Overflow or by priyanka.sarkar_2
Published on 2010-12-30T12:34:41Z Indexed on 2010/12/30 12:54 UTC
Read the original article Hit count: 151

Filed under:

I have the below table

declare @t table
(id int identity, name varchar(50),sub1 int,sub2 int,sub3 int,sub4 int)

insert into @t
select 'name1',20,30,40,50 union all
select 'name2',10,30,40,50 union all
select 'name3',40,60,100,50 union all
select 'name4',80,30,40,80 union all
select 'name5',80,70,40,50 union all
select 'name6',10,30,40,80 

The desired output should be

Id       Name      Sub1       Sub2      Sub3       Sub4

3        Name3                           100                 

4        Name4     80                                80

5        Name5      80           70                       

6        Name6                                       80

What I have done so far is

;with cteSub1 as
(
    select rn1 = dense_rank() over(order by sub1 desc),t.id,t.name,t.sub1 from @t t
)
,cteSub2 as
(
    select rn2 = dense_rank() over(order by sub2 desc),t.id,t.name,t.sub2 from @t t
)
,cteSub3 as
(
    select rn3 = dense_rank() over(order by sub3 desc),t.id,t.name,t.sub3 from @t t
)
,cteSub4 as
(
    select rn4 = dense_rank() over(order by sub4 desc),t.id,t.name,t.sub4 from @t t
)
select x1.id,x2.id,x3.id,x4.id ,x1.sub1,x2.sub2,x3.sub3,x4.sub4 from  (select c1.id,c1.sub1 from cteSub1 c1 where rn1 =1) as x1
full join (select c2.id,c2.sub2 from cteSub2 c2 where rn2 =1)x2
on x1.id = x2.id
full join (select c3.id,c3.sub3 from cteSub3 c3 where rn3 =1)x3
on x1.id = x3.id
full join (select c4.id,c4.sub4 from cteSub4 c4 where rn4 =1)x4
on x1.id = x4.id

which is giving me the output as

id  id  id  id  sub1    sub2    sub3    sub4
5   5   NULL    NULL    80  70  NULL    NULL
4   NULL    NULL    4   80  NULL    NULL    80
NULL    NULL    3   NULL    NULL    NULL    100 NULL
NULL    NULL    NULL    6   NULL    NULL    NULL    80

Help needed.

Also how can I reduce the number of CTE's?

© Stack Overflow or respective owner

Related posts about sql-server-2005