Function Max

[omaggi]

Hallo everybody. I have a problem with a query.

I need to select the max value of a field in a table and after I want to show all the record (the max value and the other field).
For exemple:

200 CH1 CAT
220 CH3 DOG

Output --> 220 CH3 DOG

So I write this query:

select max(value), id_animal, description from table

But doesn't work. The error say that id_animal and description must be included in the "aggregation function"...

Any ideas?

Thanks for the help.

Greetings!

 

[MStef]

Try this;
SELECT TOP 1 ..description frmo table

and put DESCENDING sort on the column you want the max value.

 

[omaggi]

It works.
Thanks.

Greetings.

 

[gemma-the-husky]

you cant - and its a real problem that you cant show information relative to the item that is the max - in particular you cant show the PK, which would solve most of the issues

the only way to do this is via two queries as follows, but this is still not satisfactory,

a) find the max item - 220 in your case
b) have another query that joins this query to the original data ON the 220 value, so you can select the other fields relative to the 220

and where it becomes a problem is if you have 2 candidates for the 220 - because there is no way of knowing which 220 it picked. This probably isnt a problem in your case, but in many cases, may well be. And it is caused because the join is based on some random data element, rather than the PK.

[edit

mstef - what a good idea - i never thought of using top1 to get at the other information. i can see this being a very useful idea in some circumstances]

 

[omaggi]

Ah ok. I will try tomorrow with the 2 queries, but, as you said, I will probably have a problem cause in my data there are a lot of "same number"...

Thanks for the suggestions.

Greetings Omar!