Obtaining the key column when using max

[skunk711]

I'm not sure if this is possible, but I have a query that I want to calculate the max value in the table, and return both the max value and another field in that same row.

The problem is that I can get the max value easily enough, but I can't get the id number at the same time as it spits out a message "You tried to execute a query. blah blah aggreagte expression" (i'm sure everyone is familiar with it)

Is there a way to do this in the one query???

Thanks
Ben

 

[Pat Hartman]

You need to use a subquery to obtain the Max() value. The other method is to use nested queries. The inner gets the Max() value, and the outer gets whatever other data is required by joining back to the original table.