How to get the object that have highest balance?

N1QL
,
1

this is my query
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’
#result :
[
{
“balance”: 56700000,
“uid”: “A1”
},
{
“balance”: null,
“uid”: “A2”
},
{
“balance”: 94700000,
“uid”: “A3”
},
{
“balance”: null,
“uid”: “A4”
},
{
“balance”: 94700000,
“uid”: “A5”
}
]
how can i get the object that have highest balance @dh . my expect result :
[
{
“balance”: 94700000,
“uid”: “A3”
},
{
“balance”: 94700000,
“uid”: “A5”
}
]

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