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