pick all positive least numbers from data set

Question

I have below data in a table

ID  AMOUNT  DAYS
1   10  1
1   20  2
1   30  3
1   1   4
2   34  1
2   234 2
2   234 3
2   34  4
3   3   1
3   3   2
3   23  3
3   20  4

I want below results as all amounts which have least days of a ID

ID  AMOUNT  DAYS
1   10  1
2   34  1
3   3   1

Please suggest a sql query to pick this desired output

Answer 1

For your example, you can simply do:

select t.*
from t
where t.days = 1;

If 1 is not fixed, then a correlated subquery is one method:

select t.*
from t
where t.days = (select min(t2.days) from t t2 where t2.id = t.id);

Another method is aggregation:

select t.id, min(t.days) as min_days,
       min(t.amount) keep (dense_rank first order by t.days asc) as min_amount
from t
group by t.id;

Of course row_number()/rank() is another alternative.

With an index on (id, days) and a large table, one of the above methods may be faster in practice.

Answer 2

You can use rank() function

select ID, Amount, Days from
(
 select rank() over (partition by ID order by days) as rn,
        t.*
   from tab t
)
where rn = 1;

Demo

Answer 3

First group by id to find the min days for each id and then join to the table

select t.*
from tablename t inner join (
  select id, min(days) days
  from tablename
  group by id
) g on g.id = t.id and g.days = t.days