CODEWITHSUNDEEP
sqlfirebirdgreatest-n-per-group
zac
zac

Reputation: 4918

Get another value with max

I need to get the maximum value for a field so I used this SQL in a stored procedure

Select max(field_1) from mytable into :v_max1

However I want also to get another field value with that maximum value, I could write another SQL like this

Select field_2 from mytable where field_1 = :v_max1 into v_field2

But I want to ask is it possible to get field_2 value with the first statement so I use only single statement ?

Upvotes: 1

Views: 104

Answers (3)

Alexey Kovyazin
Alexey Kovyazin

Reputation: 86

I hope you are calculating max value not to get the next value for Primary Key field in some table? This is pretty common mistake and, if so, consider to use generators (sequences) instead, since MAX() is not efficient to organize incremental sequence of values. Also, remember that MAX() can use only descending index.

Just my 2 cents :)

Regards, Alexey Kovyazin IBSurgeon

Upvotes: 1

tning
tning

Reputation: 1251

This query will return all records whose field_1 equals to MAX(field_1)

SELECT field_2 FROM mytable WHERE field_1 = (
    SELECT MAX(field_1) FROM mytable)

Upvotes: 4

1010
1010

Reputation: 1858

you can do a query like this

SELECT FIRST 1 field_1, field_2
  FROM yourtable
 ORDER BY field_1 DESC;

if I remember well you should index by field_1 in descending order for it to perform well.

note that your second query may return multiple rows if max(value_1) is not unique. this query will return only one row.

Upvotes: 2

Related Questions