Filtering by minimum value per group problem I have a select statement, which when simplified produces the following:

+------------+----------+-----+
| Group      | Name     | ID  |
| vegetables | potatoes | 1   |
| vegetables | carrots  | 6   |
| fruit      | apples   | 100 |
| fruit      | mangoes  | 97  |
+------------+----------+-----+


I would like it to produce this:

+------------+----------+----+
| Group      | Name     | ID |
| vegetables | potatoes | 1  |
| fruit      | mangoes  | 97 |
+------------+----------+----+


Turning the original select into a table is not an option. Ideally for readability, I'd like to not copy/paste the select; I'd rather alias it somehow but I feel like my syntax for that isn't quite right.

Here's my actual code. It is a horrible mess (actually if you have any suggestions for making it less of a mess that'd be great). It gets ORA-00942: table or view does not exist on the top layer - the layer below that works fine (producing the first table above, essentially)

-- first record of each brand name that has been interchanged 
SELECT *
FROM
(
-- all brand name  records that have been interchanged
SELECT DIN, DRUG_INFO_NOS.TRADE_NAME tn, FORM, GENERIC_NAME, MANUFACTURER, CR_DATE, NOC_DATE, FIRST_CLAIM_DATE, DRUG_INFO_NOS.SEQUENTIAL_ID blah
FROM
DRUG_INFO_NOS
INNER JOIN
-- Brand name drugs that have been interchanged.
(
SELECT DRUG_INFO_NOS.TRADE_NAME FROM
DRUG_INFO_NOS
INNER JOIN
-- seq_ids corresponding to interchanges
(SELECT MIN(SEQUENTIAL_ID) seq_ID FROM
 (
 --set of sequential ids not belonging to brand names
SELECT * FROM DRUG_INFO_NOS
WHERE DRUG_INFO_NOS.SEQUENTIAL_ID NOT IN
(
--set of all sequential ids belonging to brand names
SELECT SEQUENTIAL_ID FROM
DRUG_INFO_NOS
WHERE
DRUG_INFO_NOS.TRADE_NAME IN
(
--set of brand names
SELECT TRADE_NAME FROM
(
(SELECT MIN(SEQUENTIAL_ID) as seq FROM DRUG_INFO_NOS
GROUP BY GENERIC_NAME) a1

INNER JOIN
DRUG_INFO_NOS
ON a1.seq = DRUG_INFO_NOS.SEQUENTIAL_ID
  )
  )
  )
  )
  GROUP BY GENERIC_NAME) interchangeSeqIDs

  ON interchangeSeqIDS.seq_ID = DRUG_INFO_NOS.SEQUENTIAL_ID + 1) interchangeddrugs
ON DRUG_INFO_NOS.TRADE_NAME = interchangeddrugs.TRADE_NAME) interchangedbrandrecords
INNER JOIN
(SELECT tn, MIN(blah) minseq FROM interchangedbrandrecords GROUP BY tn) filter
on filter.tn = interchangedbrandrecords.tn;
To get the desired result from your sample table,

select tgroup, name, id 
from 
(
    select tgroup, name, id, row_number() over (partition by tgroup order by id) rn 
    from (&lt;&lt;your subselect&gt;&gt;) 
 ) 
 where rn = 1


I've renamed group to tgroup as group is a reserved word in SQL.

SQLFiddle

As for your real query, please first make it readable.