SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    recommendation on sql statement needed

    Is the following code (using GROUP BY and HAVING) good practise for sql or are there better ways of finding the same results:

    SELECT Max(tbl88.col1) AS MaxOfcol1
    FROM tbl88
    GROUP BY tbl88.col16
    HAVING tbl88.col16='session1'

  2. #2
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT Max(tbl88.col1) AS MaxOfcol1
    FROM tbl88
    WHERE tbl88.col16='session1'

  3. #3
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by johnjohn2
    Is the following code (using GROUP BY and HAVING) good practise for sql or are there better ways of finding the same results:

    SELECT Max(tbl88.col1) AS MaxOfcol1
    FROM tbl88
    GROUP BY tbl88.col16
    HAVING tbl88.col16='session1'
    You only need HAVING when you need to specify conditions on aggregates. For instance:
    Code:
     select * from sales;
    +------+-----------+----------+-------+
    | code | sales_rep | customer | value |
    +------+-----------+----------+-------+
    |    1 |         1 |        1 |  2010 |
    |    2 |         4 |        3 |   255 |
    |    3 |         2 |        3 |   505 |
    |    4 |         1 |        4 |   455 |
    |    5 |         3 |        1 |  3805 |
    |    6 |         1 |        2 |   505 |
    |    7 |         2 |     NULL |   675 |
    +------+-----------+----------+-------+
    
    select sales_rep, MAX(value) FROM sales GROUP BY sales_rep;
    +-----------+------------+
    | sales_rep | MAX(value) |
    +-----------+------------+
    |         1 |       2010 |
    |         2 |        675 |
    |         3 |       3805 |
    |         4 |        255 |
    +-----------+------------+
    
    select sales_rep, MAX(value) as themax  FROM sales GROUP BY sales_rep HAVING themax > 1000;
    +-----------+--------+
    | sales_rep | themax |
    +-----------+--------+
    |         1 |   2010 |
    |         3 |   3805 |
    +-----------+--------+
    The last example is saying only return me the sales rep's max value if their max value is greater than 1000. I'm guessing from your example you are looking for something like this:

    SELECT Max(tbl88.col1) AS MaxOfcol1
    FROM tbl88
    WHERE tbl88.col16='session1'
    GROUP BY tbl88.col16
    HAVING MaxOfcol1 > XXXXX

    where XXXXX is some value


Bookmarks

Posting Permissions

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