CODEWITHSUNDEEP
sqlsql-server
Rich
Rich

Reputation: 271

How to use an aggregate function on a sub-query

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

Answers (2)

JamieD77
JamieD77

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

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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

Related Questions