How to get the max value of 3 fields

Question

I have a table with integer fields like this:

Field1	Field2	Field3
6	1	2
3	6	2
7	4	2
5	6	1
1	6	2
5	7	1

1. First, I want to look for the max value in field3. It's 2.
2. Now, I want to look for the max value in field2, but only in records that have the max value from step 1. It's 6.
3. Now, I want to look for the max value in field1, but only in records that have the max value from step 2. It's 3

The result must be:

Field1	Field2	Field3
3	6	2

These numbers are just an example. They can be in range from 0 to max_int.

How to write an SQL query for Firebird 3?

Answer 1

As pointed out by user13964273 in the comments, you can use order by and fetch:

select field1, field2, field3 
from example3fields
order by field3 desc, field2 desc, field1 desc
fetch first 1 row only

dbfiddle

You can also use the window function ROW_NUMBER() with the desired order for this:

select field1, field2, field3
from (
  select field1, field2, field3, 
    row_number() over(order by field3 desc, field2 desc, field1 desc) rownum
  from example3fields
)
where rownum = 1

dbfiddle

The solution with ORDER BY is far simpler. Using a window function like DENSE_RANK could make sense if you want to find all matching rows (i.e. if there are multiple rows with the same maximum values).