'having' question

moosie · #1 (**permalink**) 09-11-09, 21:24

Hi folks. I thought I understood the 'having' clause. Guess not. Consider the following data:

| code | lstrike | sstrike |
+------+---------+---------+
| 1 | 20.00 | 22.50 |
| 0 | 20.00 | 22.50 |
| 1 | 22.50 | 25.00 |

Notice rows 1 and 2 are duplicates except for the code. I want all of these logical dupes condensed down to single rows, those with the lowest code.

I thought I could do this, but it's not working:

select code, lstrike, sstrike from aa group by lstrike, sstrike having code=min(code)

To be clear, in the above example, I want the result set to be rows 2 and 3, which are both the minimum code for their respective lstrike/sstrike pairs.

Thanks in advance.

r937 · #2 (**permalink**) 09-11-09, 22:45

you spoke of "rows" rather than values, so i'm going to guess that there might be additional columns involved, in which case you need this type of query --

Code:

SELECT aa.code
     , aa.lstrike
     , aa.sstrike    
     , aa.other_columns  
  FROM ( SELECT MIN(code) AS min_code
              , lstrike
              , sstrike 
           FROM aa
         GROUP
             BY lstrike
              , sstrike ) AS mmm
INNER
  JOIN aa
    ON aa.code    = mmm.min_code    
   AND aa.lstrike = mmm.lstrike 
   AND aa.sstrike = mmm.sstrike

if those three really are the only columns in the table, then you can simplify it to this --

Code:

SELECT MIN(code) AS code    
     , lstrike                  
     , sstrike                  
  FROM aa                       
GROUP                           
    BY lstrike                  
     , sstrike

moosie · #3 (**permalink**) 09-12-09, 00:42

Thanks for the response Rudy.
Sigh, yes there are more columns, and in the interest of providing a simple example, I went too far. And now I need to probably go too far the other way to explain why I don't know how to apply your (correct) answer to my situation:

Code:

drop table s;
drop table o;
create table s (
  ticker varchar(12),
  last decimal(6,2)
) engine=innodb;

create table o (
  sticker varchar(12),
  strike decimal(6,2)
) engine=innodb;

insert into s values('EBAY', 23.91);

insert into o values('EBAY', 5.00);
insert into o values('EBAY', 7.50);
insert into o values('EBAY', 10.00);
insert into o values('EBAY', 12.50);
insert into o values('EBAY', 14.00);
insert into o values('EBAY', 15.00);
insert into o values('EBAY', 16.00);
insert into o values('EBAY', 17.50);
insert into o values('EBAY', 19.00);
insert into o values('EBAY', 20.00);
insert into o values('EBAY', 21.00);
insert into o values('EBAY', 22.50);
insert into o values('EBAY', 24.00);
insert into o values('EBAY', 25.00);
insert into o values('EBAY', 26.00);
insert into o values('EBAY', 27.50);
insert into o values('EBAY', 30.00);
insert into o values('EBAY', 35.00);
insert into o values('EBAY', 37.50);
insert into o values('EBAY', 40.00);
insert into o values('EBAY', 45.00);
insert into o values('EBAY', 50.00);
insert into o values('EBAY', 55.00);
insert into o values('EBAY', 60.00);
insert into o values('EBAY', 65.00);

Stocks and options. Using EBAY as the example, inserted into the options table are the strike prices for call options expiring Jan 2010. My goal is to create rows containing two different strikes (for a potential 'spread' option trade). The acceptable strike pairs are defined by the conditions in this next query. The idea is that there are 'categories' of pairs ranging from 'V'ery conservative, through 'A'ggressive in terms of the trading strategy. The scale is V, C, M, A, where C is Conservative, and M is moderate.

Code:

select profile, code, l_strike, s_strike
from (

select 'V' profile, 3 code, a.strike l_strike, b.strike s_strike
from o a, o b, s
where a.sticker=b.sticker and a.sticker=s.ticker and
          s.ticker='EBAY' and a.strike <= s.last - '2.50' and
          a.strike >= s.last - ('2.50' * 3) and b.strike >= a.strike + '2.50' and
          b.strike <= a.strike + ('2.50' * 3)

UNION ALL

select 'C' profile, 1 code, a.strike l_strike, b.strike s_strike
from o a, o b, s
where a.sticker=b.sticker and a.sticker=s.ticker and
          s.ticker='EBAY' and a.strike <= s.last and
          a.strike >= s.last - ('2.50' * 2) and b.strike >= a.strike + '2.50' and
          b.strike <= a.strike + ('2.50' * 3)

UNION ALL

select 'M' profile, 0 code, a.strike l_strike, b.strike s_strike
from o a, o b, s
where a.sticker=b.sticker and a.sticker=s.ticker and s.ticker='EBAY' and
          a.strike <= s.last + '2.50' and a.strike >= s.last - '2.50' and
          b.strike >= a.strike + '2.50' and b.strike <= a.strike + ('2.50' * 3)

UNION ALL

select 'A', 2 code, a.strike l_strike, b.strike s_strike
from o a, o b, s
where a.sticker=b.sticker and a.sticker=s.ticker and s.ticker='EBAY' and
          a.strike <= s.last + ('2.50' * 2) and a.strike >= s.last  and
          b.strike >= a.strike + '2.50' and b.strike <= a.strike + ('2.50' * 3)

) 

AS m

+---------+------+----------+----------+
| profile | code | l_strike | s_strike |
+---------+------+----------+----------+
| V       |    3 |    17.50 |    20.00 | 
| V       |    3 |    17.50 |    21.00 | 
| V       |    3 |    17.50 |    22.50 | 
| V       |    3 |    19.00 |    22.50 | 
| V       |    3 |    20.00 |    22.50 | 
| V       |    3 |    17.50 |    24.00 | 
| V       |    3 |    19.00 |    24.00 | 
| V       |    3 |    20.00 |    24.00 | 
| V       |    3 |    21.00 |    24.00 | 
| V       |    3 |    17.50 |    25.00 | 
| V       |    3 |    19.00 |    25.00 | 
| V       |    3 |    20.00 |    25.00 | 
| V       |    3 |    21.00 |    25.00 | 
| V       |    3 |    19.00 |    26.00 | 
| V       |    3 |    20.00 |    26.00 | 
| V       |    3 |    21.00 |    26.00 | 
| V       |    3 |    20.00 |    27.50 | 
| V       |    3 |    21.00 |    27.50 | 
| C       |    1 |    19.00 |    22.50 | 
| C       |    1 |    20.00 |    22.50 | 
| C       |    1 |    19.00 |    24.00 | 
| C       |    1 |    20.00 |    24.00 | 
| C       |    1 |    21.00 |    24.00 | 
| C       |    1 |    19.00 |    25.00 | 
| C       |    1 |    20.00 |    25.00 | 
| C       |    1 |    21.00 |    25.00 | 
| C       |    1 |    22.50 |    25.00 | 
| C       |    1 |    19.00 |    26.00 | 
| C       |    1 |    20.00 |    26.00 | 
| C       |    1 |    21.00 |    26.00 | 
| C       |    1 |    22.50 |    26.00 | 
| C       |    1 |    20.00 |    27.50 | 
| C       |    1 |    21.00 |    27.50 | 
| C       |    1 |    22.50 |    27.50 | 
| C       |    1 |    22.50 |    30.00 | 
| M       |    0 |    22.50 |    25.00 | 
| M       |    0 |    22.50 |    26.00 | 
| M       |    0 |    22.50 |    27.50 | 
| M       |    0 |    24.00 |    27.50 | 
| M       |    0 |    25.00 |    27.50 | 
| M       |    0 |    22.50 |    30.00 | 
| M       |    0 |    24.00 |    30.00 | 
| M       |    0 |    25.00 |    30.00 | 
| M       |    0 |    26.00 |    30.00 | 
| A       |    2 |    24.00 |    27.50 | 
| A       |    2 |    25.00 |    27.50 | 
| A       |    2 |    24.00 |    30.00 | 
| A       |    2 |    25.00 |    30.00 | 
| A       |    2 |    26.00 |    30.00 | 
| A       |    2 |    27.50 |    30.00 | 
| A       |    2 |    27.50 |    35.00 | 
+---------+------+----------+----------+

The pairing conditions overlap, so for many pairs there are two acceptable designations, for instance l_strike/s_strike 20.00/22.50 is represented in both a 'C' row and a 'V' row. The codes correspond with the profile (VCMA) such that the minimum of any pair (of codes) will collapse toward 'moderate' (toward zero). I want the result set above, but with only the minimum code that represents any particular pair of strikes. No 'duplicate' pairs.

So, given all this, I don't know how to apply your answer, because the dataset isn't a table that I can subquery/self-join, but the result of a join on the fly.

Sorry for the length of this post, I hope the explanation is clear enough.
Thanks again for taking the time.
-joe

r937 · #4 (**permalink**) 09-12-09, 05:21

your query looks like this --

Code:

SELECT profile, code, l_strike, s_strike
  FROM (
       /* a bunch of stuff */
       ) AS m

change it to this --

Code:

SELECT MIN(code) AS code, l_strike, s_strike
  FROM (
       /* a bunch of stuff */
       ) AS m
GROUP
    BY l_strike, s_strike

it is possible to modify this SQL further, to stick the VCMA back on depending on the minimum code, if you need to

moosie · #5 (**permalink**) 09-12-09, 10:15

Quote:

Originally Posted by r937

it is possible to modify this SQL further, to stick the VCMA back on depending on the minimum code, if you need to

Yes, I would like to have this. This is exactly what I haven't been able to do (or I guess to explain very well

I'm open to suggestions if I'm going down a stupid path. But yes, I want the VCMA for the display. The code is just a way to rank the VCMA. There really are only the four V-C-M-A values (with a UNION for each, ugh). So, if I need a tiny table:

Code:

create table profile (
  name char(1),
  code integer(1)
)

this would be fine, but I can't figure out how to join it to the min_code in the current result set. This is what led me down the path of thinking I could use HAVING.

r937 · #6 (**permalink**) 09-12-09, 15:29

Code:

SELECT xx.profile
     , l_strike, s_strike
  FROM ( SELECT 'M' AS profile
              , 0 AS code
         UNION ALL
         SELECT 'C',1
         UNION ALL
         SELECT 'A',2
         UNION ALL
         SELECT 'V',3
       ) AS xx
LEFT OUTER
  JOIN ( SELECT MIN(code) AS code, l_strike, s_strike
           FROM (
                /* a bunch of stuff */
                ) AS m
         GROUP
             BY l_strike, s_strike
       ) AS yy
    ON yy.code = xx.code

moosie · #7 (**permalink**) 09-12-09, 22:45

Rudy,

This is perfect. Thank you!

Best,
-joe

Internet Services

Web Development

Digital Marketing

Consumer Tech