Tutorial :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.


Solution: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)


Solution: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;  


Solution:3

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 )  


Solution:4

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  


Solution: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)


Solution:6

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

will return unique rows


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »