CODEWITHSUNDEEP
sqlfirebirdfirebird-3.0
TomCat500
TomCat500

Reputation: 174

How to get the max value of 3 fields

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?

Upvotes: 1

Views: 1221

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109137

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).

Upvotes: 2

Related Questions