Reputation: 271
I haven't found an answer to this on similar questions. How can I use an aggregate function on a sub query like,
when I query a table called ratioPnLTable in my datatbase like this:
SELECT DISTINCT strat_id, ratio, root FROM ratioPnltable
WHERE totalPnl IN (SELECT DISTINCT MAX(TotalPnl) FROM ratioPnlTable
GROUP BY strat_id)
I get this results:
strat_id ratio root
1 2.6 AD
1 2.7 AD
1 2.8 AD
2 1.4 ED
2 1.5 ED
2 1.6 ED
3 1.9 HG
3 2.0 HG
3 2.1 HG
Instead I would like to have the minimum value only of ratio like this:
strat_id ratio root
1 2.6 AD
2 1.4 ED
3 1.9 HG
Upvotes: 0
Views: 89
Reputation: 13949
Shouldn't this just be a MIN() statement? Unless the Root can be different..
SELECT
strat_id,
MIN(ratio) ratio,
root
FROM
ratioPnltable
WHERE
totalPnl IN (query)
GROUP BY
strat_id,
root
Upvotes: 1
Reputation: 1271181
Based on your sample results, you can do what you want with row_number()
and no aggregation at all:
select t.*
from (select t.*,
row_number() over (partition by strat_id order by ratio) as seqnum
from ratioPnltable t
) t
where seqnum = 1;
Upvotes: 1