CODEWITHSUNDEEP
sql-servergroup-bymin
John
John

Reputation: 521

SQL - selecting min value and returning all column information for that min

I am using SQL Server 2014 Management Studio

In order to be complete I will show the full query:

SELECT 
    final_eqid.eqId, eqPrice.date_, 
    optContract.expDate, optContract.strike,
    MIN(ivListed.ivMid) iv
FROM final_eqid 
    INNER JOIN eqPrice ON final_eqid.eqId = eqPrice.eqId 
    INNER JOIN optContract ON eqPrice.eqId = optContract.eqId 
    INNER JOIN ivListed ON eqPrice.date_ = ivListed.date_ 
        AND optContract.optId = ivListed.optId
WHERE 
    eqPrice.date_ = '2008-01-02' 
    AND final_eqid.eqid = 7
GROUP BY final_eqid.eqid, eqPrice.date_, optContract.expDate, optContract.strike

I intentionally chose just one date_ and one eqid but I have thousands of each.

The results are below:

 eqId   date_                expDate           strike   iv
    7   2008-01-02 00:00:00 2008-01-19 00:00:00 25.000  0.9319
    7   2008-01-02 00:00:00 2008-02-16 00:00:00 25.000  0.6306
    7   2008-01-02 00:00:00 2008-05-17 00:00:00 25.000  0.5349
    7   2008-01-02 00:00:00 2008-08-16 00:00:00 25.000  0.5006
    7   2008-01-02 00:00:00 2008-01-19 00:00:00 30.000  0.5727
    7   2008-01-02 00:00:00 2008-02-16 00:00:00 30.000  0.5083
    7   2008-01-02 00:00:00 2008-05-17 00:00:00 30.000  0.2752
    7   2008-01-02 00:00:00 2008-08-16 00:00:00 30.000  0.4559
    7   2008-01-02 00:00:00 2008-01-19 00:00:00 35.000  0.3854
    7   2008-01-02 00:00:00 2008-02-16 00:00:00 35.000  0.4065
    7   2008-01-02 00:00:00 2008-05-17 00:00:00 35.000  0.4308
    7   2008-01-02 00:00:00 2008-08-16 00:00:00 35.000  0.4213
    7   2008-01-02 00:00:00 2008-01-19 00:00:00 40.000  0.3472
    7   2008-01-02 00:00:00 2008-02-16 00:00:00 40.000  0.37
    7   2008-01-02 00:00:00 2008-05-17 00:00:00 40.000  0.3969
    7   2008-01-02 00:00:00 2008-08-16 00:00:00 40.000  0.3953

What I am trying to get is the min iv strike and all the other information for that row. In the above data this would have ideally returned 

eqId    date_   expDate strike  iv
7   1/2/2008    1/19/2008   40  0.3472
7   1/2/2008    2/16/2008   40  0.37
7   1/2/2008    5/17/2008   30  0.2752
7   1/2/2008    8/16/2008   40  0.3953

if I do not group by strike then I get the correct numbers but lose the ability to identify the strike that had the min(iv) value

Thanks in advance for any assistance

Upvotes: 0

Views: 58

Answers (1)

Adam Porad
Adam Porad

Reputation: 14471

I think what you want is to use a Common Table Expression (CTE), which specifies a temporary result set that you can query on. The MIN function used with an OVER clause to divide the result set produced by the FROM clause into partitions to which the function is applied.

Here's an example based on your query (although I haven't tested it out, so caveat emptor):

WITH cteData AS
(
    SELECT final_eqid.eqId, 
           eqPrice.date_, 
           optContract.expDate, 
           optContract.strike,  
           ivListed.ivMid iv, 
           MIN(ivListed.ivMid) OVER (PARTITION BY final_eqid.eqId, eqPrice.date_,optContract.expDate) as MinIV
    FROM   final_eqid INNER JOIN
                 eqPrice ON final_eqid.eqId = eqPrice.eqId INNER JOIN
                 optContract ON eqPrice.eqId = optContract.eqId INNER JOIN
                 ivListed ON eqPrice.date_ = ivListed.date_ AND optContract.optId = ivListed.optId
    WHERE eqPrice.date_ ='2008-01-02' and final_eqid.eqid=7
    GROUP BY final_eqid.eqid, eqPrice.date_, optContract.expDate, optContract.strike, ivListed.ivMid
)
select eqId, date_, expDate, strike, iv
from cteData
where cteData.iv=cteData.MinIV

Upvotes: 2

Related Questions