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
add comment

5 Answers

up vote 18 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;
share|improve this answer
    
a horse with no name! what?! its name is horse by default! –  Timmerz Feb 19 at 22:34
1  
@Timmerz no much love for music, right? –  ypercube Feb 19 at 22:51
1  
I bet their skin turned red –  billinkc Feb 19 at 22:56
add comment

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
add comment

Assuming id is unique (like Leigh's answer), yet another alternative is:

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

ID                     VAL                    KIND                   
---------------------- ---------------------- ---------------------- 
2                      1337                   1                      
3                      3                      4                      
share|improve this answer
add comment

This solution uses 'keep' which was used before in many solutions, val and kind can also be simply calculated for each group.

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

select distinct
       first_value(id)   over (partition by val order by kind, id rows between unbounded preceding and unbounded following) id
     , first_value(val)  over (partition by val order by kind, id rows between unbounded preceding and unbounded following) val
     , first_value(kind) over (partition by val order by kind, id rows between unbounded preceding and unbounded following) kind
  from mytable

Uses analytical function first_value to take the value with smallest kind (order by kind) from all values (rows ...) in the group with the same val (partition by val). This is calculated for each row, so distinct is needed to get only one value per group.

share|improve this answer
add comment
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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