CODEWITHSUNDEEP
sqlsql-servergroup-by
John
John

Reputation: 521

SQL Server : selecting min value and returning all column information for that min without using CTE

I had a previous question that was answered, but when the answer was applied my tempdb.mdf file swelled to over 500 gigs. I am working with a large database but not that big (total of all tables is around 800 gigs).

In an attempt to reduce the size, I have narrowed the date down to 1 table (avoiding joins).

Here is the query I have:

SELECT
       cast ([date_] as date) date_
      ,cast ([expDate] as date) expDate
      ,[strike]
      ,[putCall]
      ,[eqId]
      ,[ivMid] iv
      ,[delta]
      ,[close_],
      MIN(ivMid) OVER (Partition by eqid, date_, expDate) as MinIV
FROM 
    [CRC].[dbo].[***minsmall]
GROUP BY 
    date_, expDate, strike, putCall, eqid, ivMid, delta, close_

and it almost gets me to where I want to be except that I don't get the particular single row with the min (iv) but rather the min(iv) added as a column to all the rows.

Here is a small sample:

date_    expDate       strike  putCall  eqId  iv   delta    close_      MinIV
2008-01-02  2008-01-19  25.000   C    7   0.9853    0.9788  37.380005   0.3472
2008-01-02  2008-01-19  25.000   P    7   0.9319    -0.0171 37.380005   0.3472
2008-01-02  2008-01-19  30.000   C    7   0.5727    0.9697  37.380005   0.3472
2008-01-02  2008-01-19  30.000   P    7   0.6316    -0.0451 37.380005   0.3472
2008-01-02  2008-01-19  35.000   C    7   0.3854    0.8062  37.380005   0.3472
2008-01-02  2008-01-19  35.000   P    7   0.4022    -0.2087 37.380005   0.3472
2008-01-02  2008-01-19  40.000   C    7   0.3472    0.199   37.380005   0.3472
2008-01-02  2008-01-19  40.000   P    7   0.366    -0.7918  37.380005   0.3472
2008-01-02  2008-02-16  25.000   C    7   0.6306    0.9752  37.380005   0.37
2008-01-02  2008-02-16  25.000   P    7   0.6463    -0.0281 37.380005   0.37
2008-01-02  2008-02-16  30.000   C    7   0.5146    0.9097  37.380005   0.37
2008-01-02  2008-02-16  30.000   P    7   0.5083    -0.0897 37.380005   0.37
2008-01-02  2008-02-16  35.000   C    7   0.4065    0.7136  37.380005   0.37
2008-01-02  2008-02-16  35.000   P    7   0.4127    -0.2923 37.380005   0.37
2008-01-02  2008-02-16  40.000   C    7   0.3801    0.3401  37.380005   0.37
2008-01-02  2008-02-16  40.000   P    7    0.37    -0.6735  37.380005   0.37

what i want is to simply extract this row :

2008-01-02  2008-01-19  40.000   C    7   0.3472    0.199   37.380005   0.3472

for each given "eqid, date_, expDate" group

I tried to add this statement:

where ivMid = MinIV

above the group by statement but I got an error message

Any help would be greatly appreciated!

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271181

Use a subquery:

SELECT t.*
FROM (SELECT cast([date_] as date) as date_, cast ([expDate] as date) as expDate,
             [strike], [putCall], [eqId], [ivMid] as iv, [delta], [close_],
             MIN(ivMid) OVER (Partition by eqid, date_, expDate) as MinIV
      FROM [CRC].[dbo].[***minsmall]
      GROUP BY date_, expDate, strike, putCall, eqid, ivMid, delta, close_
     ) t
WHERE iv = minIV;

You cannot use a column alias defined in the SELECT in the WHERE clause for that SELECT.

Upvotes: 1

Related Questions