Post reply

How to get ranks by passing variable into sp

  • September 20, 2011 at 2:06 pm

    #390444

    Hi all, I m having big time here...

    -- This is the original table

    create table ranks(studentid int,sub_id int,marks int)

    insert into ranks

    select 1,1,88 union all

    select 1,2,64 union all

    select 1,3,93 union all

    select 1,4,79 union all

    select 1,5,81 union all

    select 1,6,70 union all

    select 2,1,46 union all

    select 2,2,94 union all

    select 3,3,72 union all

    select 4,4,39 union all

    select 5,5,81 union all

    select 6,6,87 union all

    select 3,1,91 union all

    select 3,2,56 union all

    select 3,3,89 union all

    select 3,4,87 union all

    select 3,5,95 union all

    select 3,6,48

    --This is the SP I wanted to use to get the student rank

    create proc findrank(@studentid int)

    as

    select studentid,marks,RANK()over(partition by sub_id order by marks desc)as 'rank'

    from ranks

    where studentid =@studentid

    order by sub_id

    exec findrank 1

    I wanted to give ranks for the all students in each subject.

    If I run this below.. I am getting needed resutls

    select studentid,marks,RANK()over(partition by sub_id order by marks desc)as 'rank'

    from ranks

    order by sub_id

    But If I want to see individual student ranks in each subject ,I can't get them like calling the sp.

    Can someone help here please!

    I really appreciate!

  • September 20, 2011 at 2:11 pm

    #1384140

    My guess is that you need to apply the filter after the rank has been calculated ;-).

  • September 20, 2011 at 2:18 pm

    #1384148

    Can you please describe that If you can?

    Thanks

  • September 20, 2011 at 2:22 pm

    #1384153

    SELECT * FROM (Select rank, columns from table) dt where dr.id = @whatever

  • September 20, 2011 at 3:20 pm

    #1384207

    Hi Ninja's_RGR'us, Thanks for your quick reply..

    It works, But problem is here. when I was applying this

    Create procedure studentRanks(@studentid int)

    as

    select st.studentName,

    st.StudentAddress,

    (SELECT marks,ranks FROM (select studentid,marks,RANK()over(partition by sub_id order by marks desc)as 'ranks'

    from ranks) dt where dt.studentid = 1)

    from students st

    join ranks r on st.studentid = r.studentid

    where st.studentid = @studentid

    In this case, I am getting error 'subquery return more than one value'

    Thank you.

  • September 20, 2011 at 3:24 pm

    #1384211

    #1 Select rank

    #2 Use #1 as cte or derived table (not sub query)

    #3 Apply filter THEN only on studentid

    #4 Move on to next task

  • September 20, 2011 at 3:45 pm

    #1384232

    HI Ninja's_RGR'us,

    I am not sure usage of cte,as I dont work with before..

    Could you please walk me through, If possible.

    I will really appreciate.

  • September 20, 2011 at 3:52 pm

    #1384242

    SELECT * FROM (SELECT <rank goes here>, * FROM sys.objects) dtRank WHERE dtRank.object_id = 4

Viewing 8 posts - 1 through 7 (of 7 total)

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