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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select person  language      from table A      group by person  language     will return unique rows

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to distinct