1

suppose I have the following table X:

Id  Type  Name
1   1   Jane
2   2   Mary
3   3   Rose
4   4   Rachel
5   4   Darren
6   4   Jay

What will be the select statement to generate:

Id  Type  Name
1   1   Jane
2   2   Mary
3   3   Rose
4   4   Rachel

that only the first row for each type will be selected? Appreciate a lot.

yeungcase
  • 383
  • 2
  • 3
  • 12

3 Answers3

3

A simple method is to use a correlated subquery:

select t.*
from t
where t.id = (select min(t2.id) from t t2 where t2.type = t.type);

Here is the DEMO for the same.

Vishal Suthar
  • 17,013
  • 3
  • 59
  • 105
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Please try

 SELECT t.* FROM test t
 INNER JOIN (SELECT MIN(id) id FROM test GROUP BY TYPE) t2 ON  t.id = t2.id;
Pankaj Kumar
  • 550
  • 2
  • 6
  • 22
0

You may use row_number() analytic function if your database supports it.

SELECT Id
    ,Type
    ,Name
FROM (
    SELECT X.*
        ,row_number() OVER (
            PARTITION BY type ORDER BY Id
            ) rn
    FROM X
    ) a
WHERE rn = 1;

Demo

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45