How to query data in Oracle SQL that gives the max value of a column and the corresponding columns for that row?

I have a Oracle SQL query that I’m trying to get the MAX value of t4.workers column along with its corresponding columns in that row, but I only want to see one row for each project along with the max workers and the max date of the row with the max workers.

Example Data:

project	date	vendor	workers
4012	01-JAN-21	ABC	6
4012	26-JAN-21	ABC	8
4012	12-JAN-21	ABC	8
4010	13-FEB-21	ZZZ	3
4010	02-FEB-21	ZZZ	5
4010	25-FEB-21	ZZZ	15

In the above example the outcome I want to see would be:

project	date	vendor	workers
4010	25-FEB-21	ZZZ	15
4012	26-JAN-21	ABC	8

Below is my code:

SELECT
   t1.project,
   t2.date,
   t3.vendor,
   t4.workers

FROM t1, t2, t3, t4

WHERE t1 = t2
   AND t2 = t4
   AND t3 = t4;

Answer

You can use the rank() window function to assign a rank to each row per project. To define the order use the number of workers in descending order. Staring from 1 the rank is the lower the more workers there are in that row. So all the rows where this rank is 1 are the ones you want.

SELECT project,
       date,
       vendor,
       workers
       FROM (SELECT project,
                    date,
                    vendor,
                    workers,
                    rank() OVER (PARTITION BY project
                                 ORDER BY workers DESC) r
                    FROM elbat) x
       WHERE r = 1;

Note: This includes ties. If you don’t want ties you can use row_number() instead of rank but then you’d need a second criteria for the order unless it is OK for you that one random row is picked from the tied ones.