Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: 'having' question

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    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
    Last edited by moosie; 09-12-09 at 01:50.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Posts
    4
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Posts
    4
    Rudy,

    This is perfect. Thank you!

    Best,
    -joe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •