Better SELECT WHERE MAX

Question

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 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)

The good news is that there is a better solution. Read [this answer](http://stackoverflow.com/a/28090544/4265352) for details. The bad news is that I'm not in the mood to apply the technique posted there to generate the query for your case. You'll have to do it yourself :-) — axiac, Oct 12 '16 at 09:03
I will do more test later, I only have access to a Sybase here and it doesn't seems to be accepted here... — AxelH, Oct 12 '16 at 10:20

jarlh · Answer 1 · 2016-10-14T12:39:54.400

2

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

edited Oct 14 '16 at 12:39

answered Oct 12 '16 at 09:01

jarlh

42,561
8
45
63

This seems neat, thou, there is still a subquery :) – Rozkalns Oct 12 '16 at 09:46
It's "its". Just sayin' ;-) – Strawberry Oct 14 '16 at 12:39

score 0 · Answer 2 · edited Jan 18 '21 at 12:32

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

Better SELECT WHERE MAX

2 Answers2