Display the sequence numbers on the output rows of a select in Oracle 10g

[Tejas Jain]

I have an Employee table with a salary column. I want to find the distinct salaries in the Employee table.

SQL> select distinct salary from Employee order by salary;

It only display the distinct salaries:

1000
1100
12345
....

Now I want to put a line sequence number in front of each distinct salary. Something like:

1: 1000
2: 1100
3: 12345

How should I change the select query? I know rownum does not help here.

[Goutham Pallipati]

use "select rownum, distinct salary from emp order by salary"

[Ireneusz Kordal]

Try this:

SELECT ROWNUM, salary
FROM 
(
    SELECT DISTINCT salary 
    FROM employees
    ORDER BY salary
) ;

[Tejas Jain]

Goutham Pallipati wrote:use "select rownum, distinct salary from emp order by salary"

I got an ORA-00936 error: missing expression

[Tejas Jain]

Ireneusz Kordal wrote:Try this:

SELECT ROWNUM, salary
FROM 
(
    SELECT DISTINCT salary 
    FROM employees
    ORDER BY salary
) ;

This works. Thanks.

Now what if I only want to find the 3rd highest salary?

[Ireneusz Kordal]

Todd Jain wrote:

Goutham Pallipati wrote:use "select rownum, distinct salary from emp order by salary"

I go an ORA-00936 error: missing expression

You have got an error because this query has a syntax error
- DISTINCT can appear right after SELECT only ,and can applied to the whole row,
DISTINCT cannot be applied to individual columns.

The syntax of SELECT statement is:

SELECT [ DISTINCT | ALL ]
  column_expression1, column_expression2, ....
  [ FROM from_clause ]
  [ WHERE where_expression ]
  [ GROUP BY expression1, expression2, .... ]
  [ HAVING having_expression ]
  [ ORDER BY order_column_expr1, order_column_expr2, .... ]