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  |
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 #370957 is a reply to message #370939] |
Tue, 14 March 2000 01:00  |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 23:07:22 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
