CODEWITHSUNDEEP
oracle-databasedistinct
ethan
ethan

Reputation: 309

Using DISTINCT for specific columns

select distinct  employee_id, first_name,  commission_pct, department_id from
employees;

When I use the above query it results in distinct combination of all the attributes mentioned. As employee_id (being the primary key for employees) is unique, the query results in producing all the rows in the table.

I want to have a result set that has distinct combination of commission_pct and department_id. so how the query should be formed. When I tried to include the DISTINCT in the middle as 

select   employee_id, first_name, distinct commission_pct, department_id from
    employees;

It is resulting in an error

ORA-00936-missing expression

How to form a query which results have only distinct combination of commission and department_id.The table is from HR schema of oracle.

Upvotes: 2

Views: 6928

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

What you request is impossible. You cannot select all the employee ids but have only distinct commission_pct and department_id.

So think it over, what you want to show:

  • All distinct commission_pct, department_id only?
  • All distinct commission_pct, department_id and the number of relevant employees?
  • All distinct commission_pct, department_id and the relevant employees comma separated?
  • All employees, but with nulls when commission_pct and department_id are the same as in the line before?

The first can be solved with DISTINCT. The second and third with GROUP BY (plus count or listagg). The last would be solved with the analytic function LAG.

Upvotes: 2

Srini V
Srini V

Reputation: 11355

Can you try this one?

SELECT
  NAME1,
  PH
FROM
      (WITH T
           AS (SELECT
                    'mark' NAME1,
                    '1234567' PH
              FROM
                    DUAL
              UNION ALL
              SELECT
                    'bailey',
                    '456789'
              FROM
                    DUAL
              UNION ALL
              SELECT
                    'mark',
                    '987654'
              FROM
                    DUAL)
       SELECT
            NAME1,
            PH,
            ROW_NUMBER ( ) OVER (PARTITION BY NAME1 ORDER BY NAME1) SEQ
       FROM
            T)
WHERE
      SEQ = 1;

If you dont care on a specific row, then use aggregate functions 

SELECT
      NAME1,
      MAX ( PH ) PH
FROM
      T
GROUP BY
      NAME1;

Upvotes: 0

Boris Brodski
Boris Brodski

Reputation: 8695

You have to remove two columns before distinct

select distinct commission_pct, department_id from
employees;

Indeed, if your second query would work, what do you expect to see in the first two columns? Consider example data

| employee_id | first_name | commission_pct | department_id |
|     1       |     "x"    |      "b"       |       3       |
|     2       |     "y"    |      "b"       |       3       |
|     1       |     "x"    |      "c"       |       4       |
|     2       |     "y"    |      "c"       |       4       |

You expect to get only two row result like this

| employee_id | first_name | commission_pct | department_id |
|     ?       |      ?     |      "b"       |       3       |
|     ?       |      ?     |      "c"       |       4       |

But what do you expect in the first two column?

Upvotes: 1

Related Questions