Oracle Subquery Result at the Top 1

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 may be more than 1 row with the same minimum value (just select the first one)

MYTABLE

• whole (unique)
• b integer
• c integer

I tried this request

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, there may be more than one instance of the minimum value of c for a given value of b. The above query generates duplicates in these conditions.

I have 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

Answer 2

c, , min-a c b. (!)

select t0.*
FROM myTable t0
inner join (
    select t1.b, t1.c, MIN(a) as a
    from myTable t1
    inner join (
        select b, min(c) as c 
        from myTable 
        group by b
    ) t2 on t1.b = t2.b and t1.c = t2.c
    group by t1.b, t1.c
) t3 on t3.a = t0.a and t3.b = t0.b and t3.c = t0.c