Oracle subquery top 1 result

Question

I want to get the top 1 row for each unique value of b with the minimum value of c for that particular value of b. Even though there can be more than 1 row with the same min value (just chose the first one)

myTable

• a integer (unique)
• b integer
• c integer

I've tried this query

SELECT t1.* 
  FROM myTable t1, 
       (SELECT b, 
               MIN(c) as c 
          FROM myTable 
      GROUP BY b) t2 
 WHERE t1.b = t2.b 
   AND t1.c = t2.c

However, in this table it's possible for there to be more than 1 instance of the minimum value of c for a given value of b. The above query generates duplicates under these conditions.

I've got a feeling that I need to use rownum somewhere, but I'm not quite sure where.

Answer 1

You can use ROW_NUMBER:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rn
    FROM myTable
) AS T1
WHERE rn = 1