Oracle SQL find max number before repeating values start

Question

I am trying to use a query to find the maximum value in a column before repeating values start. I have three columns - year, jobnumber, hourly_rate

After a certain year, which can vary, the rates begin to repeat.

Using the data below, I need to find the year of the max rate for each jobnumber.

Desired result set:

Year	Jobnumber	hourly_rate
1998	6454	48.07765385469215
1996	1126	45.662133671135376
2000	9196	42.02154384293608

CREATE TABLE "my_GRID1_TEST" ( "YEAR" NUMBER(38,0), "JOBCODE" NUMBER(38,0), "HOURLY_RATE" NUMBER(38,15) )

Year    Jobnumber  hourly_rate
2024    1126    35.582755
2023    1126    35.96506525
2022    1126    36.3511986025
2021    1126    36.74119328852501
2020    1126    37.13508792141026
1999    1126    44.59125280927884
1998    1126    44.94554923034844
1997    1126    45.30250287457605
1996    1126    45.662133671135376
1995    1126    45.662133671135376
1994    1126    45.662133671135376
2024    9196    33.05523
2023    9196    33.05523
2022    9196    33.412265
2021    9196    33.77287035
2005    9196    39.11636105858664
2004    9196    39.42959579152604
2003    9196    39.745179784962485
2002    9196    40.0631306583497
2001    9196    40.38346616328733
2000    9196    42.02154384293608
1999    9196    42.02154384293608
1998    9196    42.02154384293608
1997    9196    42.02154384293608
2024    6454    37.494306250000005
2023    6454    37.8957320125
1999    6454    46.95322894974278
1998    6454    48.07765385469215
1997    6454    48.07765385469215
1996    6454    48.07765385469215
1995    6454    48.07765385469215
1994    6454    48.07765385469215
1993    6454    48.07765385469215

Both responses were very helpful. I ended up using the code that MT0 posted, so marked that as the accepted answer. If there is a way to split the credit, I couldn't find it. Thank you very much for taking the time to help me out with this! — Michelle H
– Michelle H, Commented Jun 24, 2024 at 19:22

MT0 · Accepted Answer · 2024-06-21 21:00:24Z

If, for each jobnumber you want to start from the latest year and look through the rows, in order, until you find a repeat and then stop and then from that set of rows find the row with the maximum hourly_rate and the corresponding year then you can use:

SELECT jobnumber,
       MAX(year) KEEP (DENSE_RANK LAST ORDER BY hourly_rate) AS year,
       MAX(hourly_rate) AS hourly_rate
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY jobnumber
         ORDER BY year DESC
         ALL ROWS PER MATCH
         PATTERN ( ^ changed+? {- (same | $) -} )
         DEFINE
           same AS PREV(hourly_rate) = hourly_rate
       )
GROUP BY
       jobnumber;

Which, for the sample data:

CREATE TABLE table_name (Year, Jobnumber, hourly_rate) AS
SELECT 2024, 1126, 35.582755 FROM DUAL UNION ALL
SELECT 2023, 1126, 35.96506525 FROM DUAL UNION ALL
SELECT 2022, 1126, 36.3511986025 FROM DUAL UNION ALL
SELECT 2021, 1126, 36.74119328852501 FROM DUAL UNION ALL
SELECT 2020, 1126, 37.13508792141026 FROM DUAL UNION ALL
SELECT 1999, 1126, 44.59125280927884 FROM DUAL UNION ALL
SELECT 1998, 1126, 44.94554923034844 FROM DUAL UNION ALL
SELECT 1997, 1126, 45.30250287457605 FROM DUAL UNION ALL
SELECT 1996, 1126, 45.662133671135376 FROM DUAL UNION ALL
SELECT 1995, 1126, 45.662133671135376 FROM DUAL UNION ALL
SELECT 1994, 1126, 45.662133671135376 FROM DUAL UNION ALL
SELECT 2024, 9196, 33.05523 FROM DUAL UNION ALL
SELECT 2023, 9196, 33.05523 FROM DUAL UNION ALL
SELECT 2022, 9196, 33.412265 FROM DUAL UNION ALL
SELECT 2021, 9196, 33.77287035 FROM DUAL UNION ALL
SELECT 2005, 9196, 39.11636105858664 FROM DUAL UNION ALL
SELECT 2004, 9196, 39.42959579152604 FROM DUAL UNION ALL
SELECT 2003, 9196, 39.745179784962485 FROM DUAL UNION ALL
SELECT 2002, 9196, 40.0631306583497 FROM DUAL UNION ALL
SELECT 2001, 9196, 40.38346616328733 FROM DUAL UNION ALL
SELECT 2000, 9196, 42.02154384293608 FROM DUAL UNION ALL
SELECT 1999, 9196, 42.02154384293608 FROM DUAL UNION ALL
SELECT 1998, 9196, 42.02154384293608 FROM DUAL UNION ALL
SELECT 1997, 9196, 42.02154384293608 FROM DUAL UNION ALL
SELECT 2024, 6454, 37.494306250000005 FROM DUAL UNION ALL
SELECT 2023, 6454, 37.8957320125 FROM DUAL UNION ALL
SELECT 1999, 6454, 46.95322894974278 FROM DUAL UNION ALL
SELECT 1998, 6454, 48.07765385469215 FROM DUAL UNION ALL
SELECT 1997, 6454, 48.07765385469215 FROM DUAL UNION ALL
SELECT 1996, 6454, 48.07765385469215 FROM DUAL UNION ALL
SELECT 1995, 6454, 48.07765385469215 FROM DUAL UNION ALL
SELECT 1994, 6454, 48.07765385469215 FROM DUAL UNION ALL
SELECT 1993, 6454, 48.07765385469215 FROM DUAL;

Outputs:

JOBNUMBER	YEAR	HOURLY_RATE
1126	1996	45.662133671135376
6454	1998	48.07765385469215
9196	2024	33.05523

(Since for jobnumber=9196 it repeats in 2024 and 2023.)

However, if you just want to find the maximum hourly_rate and the corresponding year then you can use:

SELECT jobnumber,
       MAX(year) KEEP (DENSE_RANK LAST ORDER BY hourly_rate) AS year,
       MAX(hourly_rate) AS hourly_rate
FROM   table_name
GROUP BY
       jobnumber;

or:

SELECT jobnumber, year, hourly_rate
FROM   (
  SELECT jobnumber, year, hourly_rate,
         ROW_NUMBER()
           OVER (PARTITION BY jobnumber ORDER BY hourly_rate DESC, year DESC) AS rn
  FROM   table_name
)
WHERE  rn = 1;

Which both output:

JOBNUMBER	YEAR	HOURLY_RATE
1126	1996	45.662133671135376
6454	1998	48.07765385469215
9196	2000	42.02154384293608

fiddle

shawnt00 · Accepted Answer · 2024-06-21 21:15:34Z

1

Assuming that the list always ends with the highest rate and is always ultimately repeating:

select max(year) as year, jobnumber, max(hourly_rate) as hourly_rate
from T t
where hourly_rate = (
    select max(t2.hourly_rate)
    from T t2
    where t2.jobnumber = t.jobnumber
)
group by jobnumber;

If these assumptions are invalid then please clarify or use a more robust analytic solution.

edited Jun 21, 2024 at 21:15

answered Jun 21, 2024 at 19:26

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

1 Comment

Michelle H Over a year ago

Yes, the list always ends with the highest rate. Yes, the highest rate always repeats.

Collectives™ on Stack Overflow

Oracle SQL find max number before repeating values start

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related