CODEWITHSUNDEEP
sqloracle-databasemin
user3498706
user3498706

Reputation: 1

SQL- SELECT minimum by other column

Let's have a table named employers with columns:

id
cash

and I need to select id and cash for employers that have minimal cash lower than minimal cash in rows with id = 2.

An example of the table:

----------
id    cash
----------
1      100
2      200
2      150
2      125
3      320
4      400

It should select only the first row, because minimal cash in rows with id 2 is 125 and only this row has lower cash.

I tried something like this but it didn't work:

SELECT id, MIN(cash)
FROM employers
WHERE cash < (MIN(cash) 
WHERE id = 2;

Upvotes: 0

Views: 2230

Answers (2)

user2989408
user2989408

Reputation: 3137

Try the following query

SELECT id, cash
FROM employers
WHERE cash < (SELECT MIN(cash) 
              FROM employers
              WHERE id = 2 
              GROUP BY id);

EDIT: Removed the MIN from the query, this should work with your data sample.
Also I suggest that you modify the query based on your requirements.
In case you need only the least cash available at every employer, who has less cash than the min cash, then use a GROUP BY id and select MIN(cash).

Upvotes: 1

xQbert
xQbert

Reputation: 35323

SELECT T1.ID, min(T1.cash), T2.Id, min(T2.Cash)
FROM Table T1
CROSS JOIN table T2
WHERE T1.CASH < T2.CASH
and T2.ID = 2
and T1.ID <> 2
GROUP BY T1.ID, T2.ID

Upvotes: 0

Related Questions