CODEWITHSUNDEEP
sqloracle-databasedistinct
Ian Cohen
Ian Cohen

Reputation: 576

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

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.

Upvotes: 27

Views: 125410

Answers (6)

Phil
Phil

Reputation: 1

select person, language     
from table A     
group by person, language

will return unique rows

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

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;

Upvotes: 13

Scott Swank
Scott Swank

Reputation: 684

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)

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60539

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 )

Upvotes: 9

Joe
Joe

Reputation: 818

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)

Upvotes: 47

Harper Shelby
Harper Shelby

Reputation: 16581

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

Upvotes: 2

Related Questions