CODEWITHSUNDEEP
sql
jfordummies
jfordummies

Reputation: 317

Get the min of one column but select multiple columns

I have a table as following:

ID     NAME     AMOUNT
______________________
 1      A          3
 1      B          4
 2      C          18
 4      I          2 
 4      P          9

And I want the min(Amount) for each ID but I still want to display its Name. So I want this:

 ID     NAME     min(AMOUNT)
______________________
 1      A          3
 2      C          18
 4      I          2

ID's can occur multiple times, Names too. I tried this:

SELECT ID, NAME, min(AMOUNT) FROM TABLE
GROUP BY ID

But of course its an error because I have to 

GROUP BY ID, NAME

But then I get

 ID     NAME     AMOUNT
______________________
 1      A          3
 1      B          4
 2      C          18
 4      I          2 
 4      P          9

And I understand why, it looks for the min(AMOUNT) for each combination of ID + NAME. So my question is basically, how can I select multiple column (ID, NAME, AMOUNT) and get the minimum for only one column, still displaying the others?

Im new to SQL but I cant seem to find an answer..

Upvotes: 3

Views: 3058

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

In many databases, the most efficient method uses a correlated subquery:

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

In particular, this can take advantage of an index on (id, amount).

Upvotes: 3

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

You did not specify your db vendor. If it is luckily Postgres, the problem can be also solved without nested subquery using proprietary distinct on clause:

with t(id,name,amount) as (values
 (1,      'A',          3),
 (1,      'B',          4),
 (1,      'W',          3),
 (2,      'C',          18),
 (4,      'I',          2),
 (4,      'P',          9)
)
select distinct on (id, name_of_min) id 
     , first_value(name) over (partition by id order by amount) as name_of_min
     , amount
from t
order by id, name_of_min

Just for widening knowledge. I don't recommend using proprietary features. first_value is standard function but to solve problem in simple query is still not enough. @zealous' answer is perfect.

Upvotes: 1

zealous
zealous

Reputation: 7503

If you are using PostgreSQL, SQL Server, MySQL 8.0 and Oracle then try the following with window function row_number().

in case you have one id with similar amount then you can use dense_rank() instead of row_number()

Here is the demo.

select
    id,
    name,
    amount
from
(
    select
        *,
        row_number() over (partition by id order by amount) as rnk
    from yourTable
) val
where rnk = 1

Output:

| id  | name | amount |
| --- | ---- | ------ |
| 1   | A    | 3      |
| 2   | C    | 18     |
| 4   | I    | 2      |

Second Option without using window function

select
    val.id,
    t.name,
    val.amount
from myTable t
join
(
  select
        id,
        min(amount) as amount
    from myTable
    group by
        id
) val
on t.id = val.id
and t.amount = val.amount

Upvotes: 6

Related Questions