| top n records [message #371270] |
Tue, 26 September 2000 01:10  |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
hi all,
I have a problem with sql statement.I know how to select top n salaries but not be able to prefixed with number 1,2,3........etc.if i am substracting the value with rowid like substr(rowid,13,1), it starts counting from 0 but i want from 1.how can i rectify this problem.
pls somebody help me.
|
|
|
|
|
|
|
|
| Re: top n records [message #371276 is a reply to message #371273] |
Wed, 27 September 2000 01:03  |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
thanks for your reply, but i want a bit different result.According to your reply i am getting top n salaries prefixed with rownum in disorder like this-
rownum sal
-------- ---------
4 12000
3 10000
6 8000
2 7000
but i want the result should come like this -
XXX sal
----- -------
1 12000
2 10000
3 8000
4 7000
pls help me.all the suggations will be appriciated.
Rajendra
|
|
|
|
| Re: top n records [message #371277 is a reply to message #371273] |
Wed, 27 September 2000 01:03 |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
thanks for your reply, but i want a bit different result.According to your reply i am getting top n salaries prefixed with rownum in disorder like this-
rownum sal
-------- ---------
4 12000
3 10000
6 8000
2 7000
but i want the result should come like this -
XXX sal
----- -------
1 12000
2 10000
3 8000
4 7000
pls help me.all the suggations will be appriciated.
Rajendra
|
|
|
|
| Re: top n records [message #371279 is a reply to message #371273] |
Wed, 27 September 2000 02:50 |
Letchoumy
Messages: 5
Registered: September 2000
|
Junior Member |
|
|
Try to use this the following command
select rownum,salary
from (select salary from employe group by salary)
order by salary desc
I hope that it should work.
regards.
|
|
|
|
| Re: top n records [message #371283 is a reply to message #371272] |
Wed, 27 September 2000 23:06 |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
thanks for suggation but I think you are not understanding my problem, so i am explaning whole thing.I have (emp) table having records like this-
name sal
---- ------
john 12000
hari 10000
gopal 3000
mahesh 8000
mukesh 9000
if i am using rownum it will show in the same order(salary with disorder)but i want my output should like this-
order sal
----- -------
1 12000
2 10000
3 9000
4 8000
5 3000
Any suggation?
Thanks
|
|
|
|
| Re: top n records [message #371287 is a reply to message #371273] |
Thu, 28 September 2000 06:28 |
Vineet
Messages: 10
Registered: September 2000
|
Junior Member |
|
|
Hi Rajindra,
Please Try the following Query.
select rownum , -1*salary salary from
( select -1*salary salary
from emp
group by -1*salary)
|
|
|
|
| Re: top n records [message #371294 is a reply to message #371273] |
Fri, 29 September 2000 11:13 |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Rajendra,
How abt this
select rownum, sal from (select sal, rownum from emp order by sal desc) where rownum < 10
you can replace 10 by &top. This will prompt you for the top ?
Prem :)
|
|
|
|
| Re: top n records [message #371295 is a reply to message #371273] |
Fri, 29 September 2000 11:14 |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Rajendra,
How abt this
select rownum, sal from (select sal, rownum from emp order by sal desc) where rownum < 10
you can replace 10 by &top. This will prompt you for the top ?
Prem :)
|
|
|
|
| Nothing works! [message #371297 is a reply to message #371273] |
Fri, 29 September 2000 23:58 |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
Nothing works!
all the suggestions are giving some or the other error, specifially the 'group by' posted by vineet.
the 'order by' clause with rownum in the inner query is not being recognised by sql and it is giving error.
thanks anyway!!
rajendra
|
|
|
|
| Re: top n records [message #371358 is a reply to message #371283] |
Fri, 06 October 2000 06:25 |
Sandeep Deshmukh
Messages: 13
Registered: October 2000
|
Junior Member |
|
|
Try following query to have 5 max sal
select name,sal from emp e
where 5 > (select count(*) from emp
where sal > e.sal)
order by sal desc
NB: you can use &n instead of 5 to give to have your own pick of highest salaries.
Please confirm whether it suits to ur reqmt.
Regards,
Sandeep
|
|
|
|
| Re: top n records [message #371359 is a reply to message #371283] |
Fri, 06 October 2000 06:29 |
Sandeep Deshmukh
Messages: 13
Registered: October 2000
|
Junior Member |
|
|
Try following query to have 5 max sal
select name,sal from emp e
where 5 > (select count(*) from emp
where sal > e.sal)
order by sal desc
NB:1. you can use &n instead of 5 to give to have your own pick of highest salaries.
2. For lowest salaries use < sign in subquery
Please confirm whether it suits to ur reqmt.
Regards,
Sandeep
|
|
|
|
| Re: top n records [message #371370 is a reply to message #371283] |
Sun, 08 October 2000 23:35 |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
Thanks sundeep but my query is a bit different.
I am able to select top n salaries but i want numbering along with the salaries like this-
order salary
------ -------
1 8000
2 7000
3 5000
4 5000
if i am using rownum, the result comes up like this-
order salary
------ -------
0 8000
1 7000
2 5000
3 5000
I want the order should start from 1 not from 0.
pls help...
rajendra
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
