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

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 2

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;