Subject Re: [firebird-support] Find record with max value
Author Helen Borrie
At 09:06 AM 30/08/2004 +0200, you wrote:

>I have a table with a few fields, one of them is integer type.
>I want to get a record which value of this integer field is maximal.
>What sql sentence should I use ?
>How can I be sure, that there is only one record with this max value ?
>Let say, that my values are 1,2,3,4,5,5.
>Would there be then both records with value 5 returned ?

It depends on what you ask for.

select max(MyIntColumn) from ATable
simply gets you the highest value of MyIntColumn in the whole table - you
won't know which row has that value or how many rows have it.

select * from ATable a1 where
a1.MyIntColumn = (select max(a2.MyIntColumn from ATable a2)
will get you all of the data for all of the rows having MyIntColumn equal
to the maximum value

Another way, if you have a descending index on the column, is to do
select first 1 * from aTable
order by MyIntCol desc

Other possiblities include grouped queries and, of course, any search
conditions to extract maximum values WHERE certain conditions apply and/or
HAVING a maximum group value in a certain range.