getting error as too many values in my query

Question

dpt_no   salary  period        start_date       end_date
------   -----   ------        ----------       --------
100 12580   15months    12-DEC-07   10-DEC-10
101 15500   19months    10-JAN-07   10-DEC-11
102 7777    18months    11-JUL-07   21-APR-11
103 9999    11months    07-JUL-07   31-JAN-11
104 8500    9months         12-MAR-07   27-MAR-11
105 10000   20months    17-SEP-07   01-AUG-11
106 25000   7months         17-NOV-07   26-JUL-11
107 100000  6months     05-MAY-07   21-JUN-11
108 35000   16months    28-FEB-08   21-JUN-11
109 5000    16months    02-DEC-08   19-AUG-11

i'm write query for giving rank to salary and getting that rank using ampersand. That query is

select salary from salary 
where &RANK=(select salary, rank() over(order by salary desc) 
as "rank" from salary  salary).

BUT i'm getting error as "too many values". can any one help me please

You can use this to get rank and append ampersand to it.SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RANK from salary. — Sasidharan, Aug 20 '13 at 05:51

score 1 · Accepted Answer · answered Aug 20 '13 at 05:50

1

It should be something like this:

select t1.salary from 
(select salary.salary, 
         rank() over(order by salary desc) 
as "rank" from salary) t1
where "rank"=&RANK

SQLFiddle demo

answered Aug 20 '13 at 05:50

valex

23,966
7
43
60

your query showing error as"SQl command is not properly ended" – Aug 20 '13 at 05:56
right answer.for example in salary column have multiple duplicates values.the rank function give same rank.how to rectify that – Aug 20 '13 at 06:00
@ram: In this case use ROW_NUMBER() instead if RANK() – valex Aug 20 '13 at 06:02
@ram [Dense_rank()](http://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle) – valex Aug 20 '13 at 06:18

getting error as too many values in my query

1 Answers1