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

sql oracle distinct
Ian Cohen picture Ian Cohen · Jun 11, 2009 · Viewed 119.5k times · Source

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

Joe picture Joe · Jun 11, 2009

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)

Related questions

SQL Performance: SELECT DISTINCT versus GROUP BY

I have been trying to improve query times for an existing Oracle database-driven application that has been running a little sluggish. The application executes several large queries, such as the one below, which can take over an hour to run. …

Read More
Using distinct on a column and doing order by on another column gives an error

I have a table: abc_test with columns n_num, k_str. This query doesnt work: select distinct(n_num) from abc_test order by(k_str) But this one works: select n_num from abc_test order by(k_…

Read More
Oracle SQL - How to get distinct rows using RANK() or DENSE_RANK() or ROW_NUMBER() analytic function?

I am looking to get the top 3 distinct salaries of each department. I was able to do it either using RANK() or DENSE_RANK() or ROW_NUMBER() but my table is having some records with same salaries. Mentioned below is …

Read More