Subject Re: [ib-support] How do I select a record which has the highest for a particula r column
Author Helen Borrie
At 02:21 PM 10-04-02 -0500, you wrote:
>Please help !!!
>I cant do select * from report_new(date) where attempts= max(attampts).
>Where attempts is a column..
>What is the closest way to get the entire record, I dont want to invoke the
>procedure twice ?

You are not going to guarantee a singleton row by matching the output of
max(attempts) with a row.

If you are using Firebird, you could get your row from a single DSQL

select first 1 * from source_table
where somedate = <a date constant or expression>
order by attempts desc

Otherwise, just recode the stored proc so that it passes max(attempts) into
a variable; then use the variable to extract the FIRST 1 result. (You
still need FIRST 1, since merely matching a row with max(attempts) won't
return you a singleton).

btw, date is an illegal symbol for an input argument (reserved word).


All for Open and Open for All
Firebird Open SQL Database · ·