Search code examples
sqloracle-databasedistinctrownum

Selecting Distinct field and row num just to display an id number gives duplicated data

I have a table application and it has 10 columns. category is one column and this column has duplicated values. To get distinct values I have a query

SELECT distinct(CATEGORY) as CategoryName FROM APPLICATION where applicationId=?.

I am getting result without any issue. Here now I wanted to add a another column as categoryId. There is no such field, I have to generate one. I tried with below query.

SELECT distinct(CATEGORY) as CategoryName , rownum as categoryId FROM APPLICATION where applicationId=?

Then it shows duplicate category with the rownum as id. I am ok with any number as id but category name should not duplicated. Can any one suggests how to do it in a single query.

Solution

  • please use

    SELECT CATEGORY as CategoryName, sum(rownum) FROM APPLICATION WHERE applicationId=? GROUP BY CATEGORY