Write the DB2 SQL query to find the third highest ORDER_TOTAL in a ORDERS DB2 table

DB2DatabaseBig Data AnalyticsSQL

We can find the third highest ORDER_TOTAL in the ORDERS DB2 table using the below query.

Example

SELECT ORDER_ID, MIN(ORDER_TOTAL) FROM ORDERS
   ORDER BY ORDER_TOTAL DESC
   FETCH FIRST 3 ROWS ONLY

The ‘FETCH FIRST 3 ROWS ONLY’ clause will give only 3 rows in the output and these 3 rows will be in descending order. The first row will have the highest ORDER_TOTAL in the entire ORDERS table, second row will have the second highest ORDER_TOTAL in the entire ORDERS table and so on.

The MIN aggregate function will give the least value of the ORDER_TOTAL among those 3 rows and this will be our third highest ORDER_TOTAL.

For example, if we have below ORDERS table in DB2.

ORDER_ID
ORDER_TOTAL
ORDER_DATE
Z22345
342
29-07-2020
Z62998
543
30-07-2020
Z56990
431
12-07-2020
Z56902
6743
29-07-2020
Z99781
443
10-07-2020
Z56112
889
30-07-2020

 

The described query will return the following result.

ORDER_ID
ORDER_TOTAL
Z62998
543
raja
Published on 30-Nov-2020 09:36:09
Advertisements