Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Unanswered: Incorect results while using having clause without group by

    I am running a query that has no group by clause but with 'having clause'. It is not filtering the rows properly.

    Ex:

    select * from test_history where testid = '****' and job = '****'
    and status = '****' and objtype = '*****' having enddate = max(enddate)

    This query is displaying incorrect data. Though it is supposed display the rows satisfying the condition (objtype = '*****' ) , it is showing results with all the objtype values. It is not filtering the rows properly.

    However similar query mentioned below runs fine.

    select * from test_history where testid = '****' and job = '****'
    and status = '****' and objtype = '*****' having stdate = max(sttdate)

    Is it a known bug in Sybase? We are using sybase ASE 12.5.4.

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Having works only on group by. Im surprised it doesnt throw a syntax error. Sorry Im on vacation so dont have something to test your issue with.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    That is expected behavior when you use an aggregate function without a group by see group by and having clauses
    And look at the section "Transact-SQL extensions to group by and having"

    (another example: http://www.dbforums.com/showpost.php...3&postcount=19)

Posting Permissions

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