Home » RDBMS Server » Server Administration » Using select statement find the rank...
Using select statement find the rank... [message #370939] Fri, 10 March 2000 07:07 Go to next message
N.Suresh
Messages: 3
Registered: March 2000
Junior Member
Hi,
This is Suresh.N.
I have a table named test with two cloumns name and mark.In that i have following records.I need a sql to disply name,mark and rank.I wrote one query that gives in the descending order.

Table Test
---------
Name Mark

Allen 78
Miller 100
Jhon 67
Jones 100
Adam 98

The select statment query result should be like following

Name Mark Rank

Miller 100 1
Jones 100 1
Adam 98 2
Allen 78 3
Jhon 67 4

Regards
Suresh.N
Re: Using select statement find the rank... [message #370945 is a reply to message #370939] Sat, 11 March 2000 01:14 Go to previous messageGo to next message
shravan
Messages: 13
Registered: March 2000
Junior Member
may be fulltext indexing should help you
Re: Using select statement find the rank... [message #370948 is a reply to message #370939] Sat, 11 March 2000 06:38 Go to previous messageGo to next message
Suresh
Messages: 189
Registered: December 1998
Senior Member
i.e .. Fulltext indexing means ,indexing name and mark columns. how it will give the rank ?

Plese give the sql query ?
Re: Using select statement find the rank... [message #370957 is a reply to message #370939] Tue, 14 March 2000 01:00 Go to previous messageGo to next message
Suresh.N
Messages: 41
Registered: March 2000
Member
Hello,

Your Qurey is..

SQL> SELECT NAME, MARK, DECODE(TRUNC(MARK/10),10,1,
9,2,
8,2,
7,3,
6,4,
5,5,
4,6,
3,7,
2,8,
1,9,
0) RANK FROM TABLE_NAME
ORDER BY RANK DESC

...

Sorry to say .. You have put rank 2 for both 9 and 8.

The modified one is..
SELECT NAME, MARK,
DECODE(TRUNC(MARK/10),10,1,
9,2,
8,3,
7,4,
6,5,
5,6,
4,7,
3,8,
2,9,
1,10,
null) RANK FROM TABLE_NAME
ORDER BY RANK DESC

If suppose the mark is between 0 to 10 the rank
will be 0 or null.If the mark is between 10 to 20 the rank will be 10.

Thank you.

Reagrds
N.Suresh
Re: Using select statement find the rank... [message #370960 is a reply to message #370939] Tue, 14 March 2000 13:14 Go to previous message
Roshan D'Souza
Messages: 8
Registered: March 2000
Junior Member
How bout trying this query Suresh....I hope it works out for you.....if you have a large number of rows its may prove to be very slow. Quick way would be certainly PL/SQL but if you want a sql query than here's it.

prerequisite
Table : test
Columns :
name varchar2(15)
mark number

select name,mark,max(rank) rank
from
(select name,mark,level rank
from test
connect by prior mark > mark
start with mark=(select max(mark) from test))
group by name,mark
order by mark desc

Let me know if it works out

Roshan
Previous Topic: Re: Free Blocks in a single sql query, Suresh
Next Topic: very strang errors from a very simple trigger
Goto Forum:
  


Current Time: Wed Oct 21 11:53:52 CDT 2020