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”
}
]
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