10 added links to KEEP docs
source | link

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

select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val;
ID |  VAL | KIND
-: | ---: | ---:
 3 |    3 |    4
 2 | 1337 |    1

dbfiddle here

KEEP…FIRST and KEEP…LAST are an Oracle-specific feature of aggregates — you can read about then here in the Oracle docs, or on ORACLE_BASE:

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence

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

select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val;
ID |  VAL | KIND
-: | ---: | ---:
 3 |    3 |    4
 2 | 1337 |    1

dbfiddle here

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

select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val;
ID |  VAL | KIND
-: | ---: | ---:
 3 |    3 |    4
 2 | 1337 |    1

dbfiddle here

KEEP…FIRST and KEEP…LAST are an Oracle-specific feature of aggregates — you can read about then here in the Oracle docs, or on ORACLE_BASE:

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence

9 switched from SQLFiddle to dbfiddle
source | link

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

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

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

SQLFiddle heredbfiddle here

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

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

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

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

dbfiddle here

8 http://chat.stackexchange.com/transcript/message/21831139#21831139
source | link

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) id
     , val
     , min(kind) kind
  from mytable
 group by val

SQLFiddle here

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) id
     , val
     , min(kind) kind
  from mytable
 group by val

SQLFiddle here

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

7 Rollback to Revision 5
source | link
6 Rollback to Revision 4
source | link
5 simplified by removing the more complex example - this is now really the canonical way of doing this
source | link
4 merged other answer in
source | link
3 added 129 characters in body
source | link
2 added 4 characters in body
source | link
1
source | link