Oracle 11g SQL to get unique values in one column of a multi-column query

Question

Given a table A of people, their native language, and other columns C3 .. C10 represented by ...

Table A

PERSON   LANGUAGE   ...
bob      english
john     english
vlad     russian
olga     russian
jose     spanish

How do I construct a query which selects all columns of one row for each distinct language?

Desired Result

PERSON   LANGUAGE   ...
bob      english
vlad     russian
jose     spanish

It doesn't matter to me which row of each distinct language makes the result. In the result above, I chose the lowest row number of each language.

Answer 1

I'd use the RANK() function in a subselect and then just pull the row where rank = 1.

select person, language
from
( 
    select person, language, rank() over(order by language) as rank
    from table A
    group by person, language
)
where rank = 1

Answer 2

My Oracle is a bit rusty, but I think this would work:

SELECT * FROM TableA
WHERE ROWID IN ( SELECT MAX(ROWID) FROM TableA GROUP BY Language )

Answer 3

If it doesn't matter which row to select, I'd go with a simple group by:

SQL> WITH DATA AS (
  2     SELECT 'bob' person, 'english' language FROM dual UNION ALL
  3     SELECT 'john', 'english' FROM dual UNION ALL
  4     SELECT 'vlad', 'russian' FROM dual UNION ALL
  5     SELECT 'olga', 'russian' FROM dual UNION ALL
  6     SELECT 'jose', 'spanish' FROM dual
  7  )
  8  SELECT MIN(person) person, language FROM DATA GROUP BY LANGUAGE;

PERSON LANGUAGE
------ --------
bob    english
jose   spanish
olga   russian

Answer 4

Eric Petroelje almost has it right:

SELECT * FROM TableA
WHERE ROWID IN ( SELECT MAX(ROWID) FROM TableA GROUP BY Language )

Note: using ROWID (row unique id), not ROWNUM (which gives the row number within the result set)

Answer 5

For efficiency's sake you want to only hit the data once, as Harper does. However you don't want to use rank() because it will give you ties and further you want to group by language rather than order by language. From there you want add an order by clause to distinguish between rows, but you don't want to actually sort the data. To achieve this I would use "order by null" E.g.

count(*) over (group by language order by null)

Answer 6

This will be more efficient, plus you have control over the ordering it uses to pick a value:

SELECT DISTINCT
       FIRST_VALUE(person)
          OVER(PARTITION BY language
               ORDER BY person)
      ,language
FROM   tableA;

If you really don't care which person is picked for each language, you can omit the ORDER BY clause:

SELECT DISTINCT
       FIRST_VALUE(person)
          OVER(PARTITION BY language)
      ,language
FROM   tableA;