aspose file tools*
The moose likes JDBC and the fly likes Display the sequence numbers on the output rows of a select in Oracle 10g Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Display the sequence numbers on the output rows of a select in Oracle 10g" Watch "Display the sequence numbers on the output rows of a select in Oracle 10g" New topic
Author

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

Tejas Jain
Ranch Hand

Joined: Mar 04, 2008
Posts: 119
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.

"Knowing is not enough, you must apply... Willing is not enough, you must do."
--Bruce Lee
Goutham Pallipati
Greenhorn

Joined: Aug 13, 2008
Posts: 20
use "select rownum, distinct salary from emp order by salary"
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Try this:
Tejas Jain
Ranch Hand

Joined: Mar 04, 2008
Posts: 119
Goutham Pallipati wrote:use "select rownum, distinct salary from emp order by salary"


I got an ORA-00936 error: missing expression
Tejas Jain
Ranch Hand

Joined: Mar 04, 2008
Posts: 119
Ireneusz Kordal wrote:Try this:


This works. Thanks.

Now what if I only want to find the 3rd highest salary?
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
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:
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Display the sequence numbers on the output rows of a select in Oracle 10g
 
Similar Threads
getters and setters provide abstraction or encapsulation ?
Query and sub query question in Oracle SQL.
Query -- select maximum salary in a sum table
SQL: Retrieve duplicate records
counting slaries using query