[teterin]

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:

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:

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!

[r937]

select * 
  from dbo.t_mx_bond_quotes 
 where ID =
       ( select MAX(ID)
           from dbo.t_mx_bond_quotes )

[teterin]

Originally Posted by r937

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!

[r937]

put an index on ID and the subquery should do an index seek (or whatever that's called in sybase)

alternatively,

select top 1 * 
  from dbo.t_mx_bond_quotes 
order
    by ID desc