How to get the object that have highest balance?

SQL++
,
2

It is by no means the only or best way of doing it, but:

WITH balances AS (
  SELECT w.uid, (SELECT SUM(t.amount) balance FROM db.core.transactions t WHERE t.wid=META(w).id)[0].balance
  FROM db.core.wallets w
  WHERE w.wallet='dealer'
)
SELECT b.uid, b.balance
FROM balances b
WHERE b.balance = (SELECT RAW MAX(b2.balance) FROM balances b2)[0];

should work.

If w.uid is expected to be unique in the results then:

WITH balances AS (
  SELECT w.uid, SUM(t.amount) balance
  FROM db.core.wallets w LEFT OUTER JOIN db.core.transactions t ON t.wid=META(w).id
  WHERE w.wallet='dealer'
  GROUP BY w.uid
)
SELECT b.uid, b.balance
FROM balances b
WHERE b.balance = (SELECT RAW MAX(b2.balance) FROM balances b2)[0];

will probably be a better way of doing it. (If w.uid is not unique in the results, then add meta(w).id to the start of the CTE’s select-list and its GROUP BY.)

(You of course need suitable indices too.)

HTH.

2 Likes