Post reply

I need to get the rank 0f the students without using the rank functions

  • March 20, 2012 at 12:45 am

    #266056

    Hi,

    create table student(s_id number,s_name varchar2(20),marks number);

    insert into student values(1,'XXX',1000);

    insert into student values(2,'YYY',3999);

    insert into student values(7,'YY',999);

    so on for n number of records....

    o/p:

    s_id marks somecolname

    2 3999 1

    1 1000 2

    7 999 3

    Thanks in Advance..

  • March 20, 2012 at 12:49 am

    #1461459

    select *,ROW_NUMBER() OVER (ORDER BY marks desc)ranks from student

    Regards

    Guru

  • March 20, 2012 at 12:52 am

    #1461460

    Hi Thanks,

    I need the result without using any predefined functions like row_number(),rank(),dense_rank(),ntile()..

  • March 20, 2012 at 1:02 am

    #1461462

    Hi thanks got it..

    select

    empno,

    ename,

    deptno,

    sal,

    (select count(sal)+1 from emp b where b.deptno=a.deptno and b.sal < a.sal) rnk

    from

    emp a

    order by

    deptno, sal;

  • March 20, 2012 at 1:12 am

    #1461466

    create table #temp (ranks int identity ,s_id int,s_name varchar(20),marks numeric(9,0))

    Insert into #temp select * from student order by marks desc

    select * from #temp

    drop table #temp

  • March 20, 2012 at 2:23 am

    #1461487

    Why can't you use the windowing functions? Homework limitation?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 20, 2012 at 7:14 pm

    #1461996

    nkcherukuri (3/20/2012)

    Hi thanks got it..

    select

    empno,

    ename,

    deptno,

    sal,

    (select count(sal)+1 from emp b where b.deptno=a.deptno and b.sal < a.sal) rnk

    from

    emp a

    order by

    deptno, sal;

    Please read the following article to see why that's probably the worst way to do such a thing.

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 20, 2012 at 7:19 pm

    #1461997

    GuruGPrasad (3/20/2012)

    create table #temp (ranks int identity ,s_id int,s_name varchar(20),marks numeric(9,0))

    Insert into #temp select * from student order by marks desc

    select * from #temp

    drop table #temp

    SELECT/INTO would be faster and cause less logging in TempDB.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 21, 2012 at 12:37 am

    #1462025

    nkcherukuri (3/20/2012)

    Hi Thanks,

    I need the result without using any predefined functions like row_number(),rank(),dense_rank(),ntile()..

    Tell your professor/teacher that it is stupid to put such limitations on their assignments.

    What does it teach? How to write inefficient code? The functions are there, use them.

    (this reminds me of teachers saying: "you won't always have a calculator!". Euh, yes I do.)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 21, 2012 at 12:48 am

    #1462028

    Thank you Jeff Moden

  • March 21, 2012 at 3:30 am

    #1462061

    Please read the following article to see why that's probably the worst way to do such a thing.

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    This woke me up from a nap after lunch...

Viewing 11 posts - 1 through 11 (of 11 total)

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