CODEWITHSUNDEEP
sqlsql-serversql-server-2008
euge1220
euge1220

Reputation: 235

SQL finding second largest value in a table

Tbl1 looks like: 

    CUSIP_ID1   CUSIP_ID2   cor       dt_pnts 
    00768Y818   00162Q726   0.974691   252
    00768Y818   00162Q205   0.874761   4
    00768Y818   00162Q103   0.774691   48  
    73935X153   00162Q726   0.979131   39
    73935X153   132061201   0.975207   252
    73935X153   34416W866   0.967654   152
    739371102   464287671   0.937278   252
    739371102   464287309   0.935797   252
    78467V103   33939L407   0.951472   35
    78467V103   78463X541   0.930144   252
    78467V103   57060U795   0.923911   108

My code is: (tbl3 is just a reference table for the ticker) 

insert into tbl2 (ticker, cusip_id, maxcor, dt_pnts)
    select b.Ticker, a.CUSIP_ID1 No_indx_cusip, max(abs(a.cor)) maxcor, dt.dt_pnts
    from  tbl1 a 
    inner join tbl3 b on
        a.CUSIP_ID1 = b.CUSIP_ID and a.dt_pnts > 20 
    inner join 
        (
           select cusip_id1, cor, dt_Pnts 
           from tbl1
        ) dt ON a.CUSIP_ID1 = dt.CUSIP_ID1
    group by a.CUSIP_ID1, b.Ticker, dt.dt_pnts, dt.cor
    having abs(dt.cor) = MAX(abs(a.cor))

select * from tbl2

it just finds the maximum correlation value of each ticker/cusip_id with its respective date points to return:

ticker  cusip_id   maxcor  dt_pnts

TTFS    00768Y818   0.974691   252
PXLG    739371102   0.937278   252
INKM    78435X153   0.979131   39
RLY     78467V103   0.951472   35

However, I would like to find the value of the 2nd largest correlation (cor) given the same condition (that dt_pnts is > 20) for each CUSIP_ID1. I tried messing around with dense_rank() a little but I'm still a beginner so i need help (please!)

return would be: 

ticker  cusip_id   maxcor  dt_pnts

TTFS    00768Y818   0.774681   48
PXLG    739371102   0.935797   252
INKM    78435X153   0.975207   252
RLY     78467V103   0.923911   108

Upvotes: 1

Views: 291

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You are better off using row_number() to get this information:

select ticker, No_indx_cusip, cor, dt_pnts
from (select b.Ticker, a.CUSIP_ID1 as No_indx_cusip, a.cor, a.dt_pnts,
             row_number() over (partition by b.ticker order by abs(a.cor) desc) as seqnum
      from  tbl1 a 
      inner join tbl3 b
            on a.CUSIP_ID1 = b.CUSIP_ID and a.dt_pnts > 20
     ) t
where seqnum = 2;

This is the query. If you want to insert this into a table, just use insert as in the question.

Upvotes: 4

Related Questions