27

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.

Improve this question

6 Answers 6

Reset to default
47

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 good catch! - I've been out of the Oracle world for too long now.
It'll work but it's a self join - the analytic function will do the same job but more efficiently.
13

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;
Improve this answer

2 Comments

can this be adapted to select the first value of several other columns as well? I alluded to these as "other columns C3 .. C10 represented by ..." in my question. Thanks!
yes. SELECT DISTINCT FIRST_VALUE(colA) OVER (PARTITION BY x), FIRST_VALUE(colB) OVER (PARTITION BY x), ... FROM tableA
9

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 )
Improve this answer

4 Comments

Can you GROUP BY a column that's not in the SELECT list?
@tekBlues - sure, why not? :)
@Eric Petroelje: this won't work in Oracle unfortunately. The rownum pseudo-column is calculated after the select. So suppose the inline query returns the rows (2,4,5) : The outer query will never return any row because the first row of a select is always rownum=1 and this will not satisfy {rownum in (2,4,5)}
@Vincent - My mistake, I should have used ROWID rather than ROWNUM. Corrected my answer to reflect that.
2

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
Improve this answer

Comments

0

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)

Improve this answer

2 Comments

Scott, the group by won't work here, the synthax for an analytical query is partition by. You could also use row_number() instead of count(*) (more readable maybe?)
That's what I get for typing off the top of my head. And you're right on both counts.
0 
select person, language     
from table A     
group by person, language

will return unique rows

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.