10 added links to KEEP docs edited Mar 23 '17 at 10:51 Jack Douglas 33.2k1313 gold badges8585 silver badges159159 bronze badges 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 edited Mar 22 '17 at 9:37 Jack Douglas 33.2k1313 gold badges8585 silver badges159159 bronze badges 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 edited May 26 '15 at 16:25 Jack Douglas 33.2k1313 gold badges8585 silver badges159159 bronze badges 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 edited May 25 '15 at 19:21 Jack Douglas 33.2k1313 gold badges8585 silver badges159159 bronze badges 6 Rollback to Revision 4 edited May 25 '15 at 19:19 Jack Douglas 33.2k1313 gold badges8585 silver badges159159 bronze badges 5 simplified by removing the more complex example - this is now really the canonical way of doing this edited May 25 '15 at 19:17 Jack Douglas 33.2k1313 gold badges8585 silver badges159159 bronze badges 4 merged other answer in edited Nov 13 '13 at 12:54 Jack Douglas 33.2k1313 gold badges8585 silver badges159159 bronze badges 3 added 129 characters in body edited Nov 12 '13 at 21:45 dezso 24.4k1212 gold badges6565 silver badges105105 bronze badges 2 added 4 characters in body edited Oct 30 '13 at 16:39 mik 40644 silver badges33 bronze badges 1 answered Oct 30 '13 at 16:24 mik 40644 silver badges33 bronze badges