Select column not on a group by statement-CodePudding

So, I have a table called RACES in which I have the following columns

raceid | year | round

I want to get the raceid for the last race of each year, which would have the highest value in the round column for each year. So I've done the following query:

SELECT
    year,
    max(round)
FROM races
group by year
order by year;

The result is:

year    max(round)
1950    7
1951    8
1952    8
1953    9
1954    9

Now if I do

SELECT * FROM RACES WHERE YEAR = 1950;

I get this:

raceid  year    round
833     1950    1
834     1950    2
835     1950    3
836     1950    4
837     1950    5
838     1950    6
839     1950    7

Since the number of rounds are variable with time, 1951 might not have 7 rounds but 10. So I want always to get the max value of rounds for a specific year and then print it along with the corresponding raceid of that round. In this case it would be 839

Since raceid is not in the group by, I can't select it in the first query. How can I do this?

CodePudding user response：

select year, max(round) as max_round, 
       max(raceid) keep (dense_rank last order by round) as raceid
from   races
group  by year
order  by year
;

Documentation for first and last function:

https://docs.oracle.com/database/121/SQLRF/functions074.htm#SQLRF00641

CodePudding user response：

With sample data you posted, that's just another MAX function call:

SQL> with races (raceid, year, round) as
  2    (select 700, 1940, 1 from dual union all
  3     select 701, 1940, 2 from dual union all
  4     --
  5     select 833, 1950, 1 from dual union all
  6     select 838, 1950, 6 from dual union all
  7     select 839, 1950, 7 from dual
  8    )
  9  select year,
 10        max(round) max_round,
 11        max(raceid) max_raceid
 12  from races
 13  group by year;

      YEAR  MAX_ROUND MAX_RACEID
---------- ---------- ----------
      1940          2        701
      1950          7        839

SQL>

However, if RACEID isn't MAX along with ROUND, then you could use your current query as a subquery (or a CTE) and join it with the "original" table:

SQL> with races (raceid, year, round) as
  2    (select 700, 1940, 1 from dual union all
  3     select 701, 1940, 2 from dual union all
  4     --
  5     select 900, 1950, 1 from dual union all  --> 900 is MAX for year 1950
  6     select 838, 1950, 6 from dual union all
  7     select 839, 1950, 7 from dual
  8    ),
  9  mrid as
 10    (select year,
 11            max(round) max_round
 12     from races
 13     group by year
 14    )
 15  select m.year,
 16         m.max_round,
 17         r.raceid
 18  from mrid m join races r on r.year = m.year and r.round = m.max_round;

      YEAR  MAX_ROUND     RACEID
---------- ---------- ----------
      1940          2        701
      1950          7        839

SQL>