Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Question Unanswered: HAVING column=MAX(column) ?

    Hello!

    I'd like to be able to extract the most recent entries from the table and the construct in subject works with Sybase. For example:

    Code:
    select * from _DBF having M_IDENTITY=MAX(M_IDENTITY)
    When trying to port the same query to the SQL-server, however, I get an error:

    Code:
    1> select * from  dbo.t_mx_bond_quotes having ID=MAX(ID)
    2> go
    Msg 8121, Level 16, State 1, Server ADSRV215, Line 1
    Column 'dbo.t_mx_bond_quotes.ID' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
    This suggests, my query is somehow not quite fully standard -- how do I change it to be proper SQL?

    Thanks!
    If you ever back up Sybase, you want this backup-server plugin.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select * 
      from dbo.t_mx_bond_quotes 
     where ID =
           ( select MAX(ID)
               from dbo.t_mx_bond_quotes )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    Quote Originally Posted by r937
    Code:
    select * 
      from dbo.t_mx_bond_quotes 
     where ID =
           ( select MAX(ID)
               from dbo.t_mx_bond_quotes )
    Yes, I know, but this scans the table twice -- the number of "logical reads" equals twice the number of rows. Is there really no way to avoid a subquery? Thanks!
    If you ever back up Sybase, you want this backup-server plugin.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    put an index on ID and the subquery should do an index seek (or whatever that's called in sybase)

    alternatively,
    Code:
    select top 1 * 
      from dbo.t_mx_bond_quotes 
    order
        by ID desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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