Oracle, Select Distinct columns, with corresponding columns-CodePudding

I want to select DISTINCT results from the user_id column but I need the corresponding columns as well.

Result set needs to return two role_id that are Distnct user_id and be not an 'Unassigned' status.

The query I am using:

SELECT role_id, user_id, role_code, status_code FROM table where school_id=5 and status_code= 'DRAFT';

This an example of my table:

ROLE_ID USER_ID SCHOOL_ID CAMPUS_ID ROLE_CODE STATUS_CODE 
   1       4        5         7      Unassigned   DRAFT
   2       4        5         7        TEST       DRAFT
   3       4        5         8        TEST       DRAFT
   4       5        5         9      Unassigned  DRAFT
   5       5        5         9        TEST       DRAFT
   6       5        5         10       TEST       DRAFT

I have tried to add group by based on user_id but I get an ORA-00979.

CodePudding user response：

You can use ROW_NUMBER() to identify the rows you want. For example:

select *
from (
  select t.*,
    row_number() over(partition by user_id order by role_id) as rn
  from t
  where role_code <> 'Unassigned'
) x
where rn = 1

CodePudding user response：

DISTINCT is across the entire set of columns and not for one specific column. Therefore, if you want to get the DISTINCT rows which are not Unassigned you can use:

SELECT DISTINCT
       role_id,
       user_id,
       role_code,
       status_code
FROM   table
where  school_id   = 5
and    status_code = 'DRAFT'
and    role_code   != 'Unassigned';

If you want to get a single row for each user_id then you can use GROUP BY and find the minimum role_id:

SELECT MIN(role_id) AS role_id,
       user_id,
       MIN(role_code  ) KEEP (DENSE_RANK FIRST ORDER BY role_id) AS role_code,
       MIN(status_code) KEEP (DENSE_RANK FIRST ORDER BY role_id) AS status_code
FROM   table
where  school_id   = 5
and    status_code = 'DRAFT'
and    role_code   != 'Unassigned'
GROUP BY
       user_id;