CODEWITHSUNDEEP
mysqlsqlselectmax
Rozkalns
Rozkalns

Reputation: 512

Better SELECT WHERE MAX

Is there any better solution to get these multiple (sometimes it would be just one row, sometimes - multiple) rows with the minimal amount value without subquery? If there is no better solutios, that's fine, just a little bit sad. :)

My Table

My first solution is like this (I don't like subquery)

SELECT * FROM transactions
WHERE wallet_id = 148
  AND amount = (SELECT MIN(amount) FROM transactions WHERE wallet_id = 148)

Result

Upvotes: 1

Views: 108

Answers (2)

jarlh
jarlh

Reputation: 44786

General query to find each wallet with its minimum amount:

SELECT t1.*
FROM transactions t1
JOIN (SELECT wallet_id, MIN(amount) minamount
      FROM transactions GROUP BY wallet_id) t2
    on t1.wallet_id = t2.wallet_id and t1.amount = t2.minamount

WHERE t1.wallet_id = 148  -- keep or remove this line

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35603

Sometimes called a "LEFT Excluding JOIN" (and here it is a also a "self join" as we use the table to join to itself). The conditions of the join require that t1.amount is greater than t2.amount and then through the where clause we locate the rows that have no match, hence we have the lowest value of t1.amount.

SELECT
      t1.*
FROM transactions t1
LEFT JOIN transactions t2 ON t1.wallet_id = t2.wallet_id AND t1.amount > t2.amount
WHERE t1.wallet_id = 148 AND t2.wallet_id IS NULL

see: https://data.stackexchange.com/stackoverflow/query/556758/left-excluding-self-join

also see diagram of Left excluding JOIN

nb: I don't claim this to be better or "correct"; just that it is an alternative

Upvotes: 0

Related Questions