Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table like this:

 ID |  Val   |  Kind
----------------------
 1  |  1337  |   2
 2  |  1337  |   1
 3  |   3    |   4
 4  |   3    |   4

I want to make a SELECT that will return just the first row for each Val, ordering by Kind.

Sample output:

 ID |  Val   |  Kind
----------------------
 2  |  1337  |   1
 3  |   3    |   4

How can I build this query?

share|improve this question
    
why 3|3|4 and not 4|3|4 - what is the tie-break or do you not care? –  Jack Douglas Sep 30 '11 at 18:46
    
@JackDouglas Actually I have a ORDER BY ID DESC, but that is not relevant for the question. In this example I do not care. –  BrunoLM Sep 30 '11 at 21:54

5 Answers 5

up vote 26 down vote accepted

Use a common table expression (CTE) and a windowing/ranking/partitioning function like ROW_NUMBER.

This query will create an in-memory table called ORDERED and add an additional column of rn which is a sequence of numbers from 1 to N. The PARTITION BY indicates it should restart at 1 every time the value of Val changes and we want to order rows by the smallest value of Kind.

WITH ORDERED AS
(
SELECT
    ID
,   Val
,   kind
,   ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Kind ASC) AS rn
FROM
    mytable
)
SELECT
    ID
,   Val
,   Kind
FROM
    ORDERED
WHERE
    rn = 1;

Update 2015-05-25

The above approach should work with any RDBMS that has implemented the ROW_NUMBER() function. Oracle has some elegant functionality as expressed in mik's answer below that will generally yield better performance than this answer.

share|improve this answer

bilinkc's solution works fine, but I thought I'd toss mine out as well. It has the same cost, but might be faster (or slower, I haven't tested it). The difference is that it uses the First_Value instead of Row_Number. Since we are only interested in the first value, in my mind it is more straightforward.

SELECT ID, Val, Kind FROM
(
   SELECT First_Value(ID) OVER (PARTITION BY Val ORDER BY Kind) First, ID, Val, Kind 
   FROM mytable
)
WHERE ID = First;

Test Data.

--drop table mytable;
create table mytable (ID Number(5) Primary Key, Val Number(5), Kind Number(5));

insert into mytable values (1,1337,2);
insert into mytable values (2,1337,1);
insert into mytable values (3,3,4);
insert into mytable values (4,3,4);

If you prefer, here is the CTE equivalent.

WITH FirstIDentified AS (
   SELECT First_Value(ID) OVER (PARTITION BY Val ORDER BY Kind) First, ID, Val, Kind 
   FROM mytable
   )
SELECT ID, Val, Kind FROM FirstIdentified
WHERE ID = First;
share|improve this answer
1  
+1 but I just thought it worth emphasising that your answer and billinkc's are not logically the same unless id is unique. –  Jack Douglas Sep 30 '11 at 19:01
    
@Jack Douglas - True, I assumed that. –  Leigh Riffel Oct 1 '11 at 0:02

You can use keep to select an id from each group:

select *
from mytable
where id in ( select min(id) keep (dense_rank first order by kind, id)
              from mytable
              group by val );

ID                     VAL                    KIND                   
---------------------- ---------------------- ---------------------- 
2                      1337                   1                      
3                      3                      4                      

SQLFiddle Here

share|improve this answer

This solution also uses keep, but val and kind can also be simply calculated for each group without a CTE:

select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val

SQLFiddle here

share|improve this answer
SELECT MIN(MyTable01.Id) as Id,
       MyTable01.Val     as Val,
       MyTable01.Kind    as Kind 
  FROM MyTable MyTable01,                         
       (SELECT Val,MIN(Kind) as Kind
          FROM MyTable                   
      GROUP BY Val) MyTableGroup
WHERE MyTable01.Val  = MyTableGroup.Val
  AND MyTable01.Kind = MyTableGroup.Kind
GROUP BY MyTable01.Val,MyTable01.Kind
ORDER BY Id;
share|improve this answer
    
That will be a lot less efficient than the other answers due to the fact that two scans over MyTable are needed. –  a_horse_with_no_name Nov 22 '13 at 9:49
    
That's only true if the optimizer takes the written query literally. More advanced optimizers can see the intent (row per group) and produce a plan with a single table access. –  Paul White yesterday

protected by Paul White yesterday

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.