How to use "Partition By" or "Max"?

sql oracle max

52,747

Solution 1

select year, x,y
from (
      select year, x, y, max(year) over(partition by x) max_year
      from my data
      )
where  year = max_year

Solution 2

select * from (
  select year, x, y, row_number() over (partition by x order by year desc ) rn 
  from my_data
) where rn = 1

Solution 3

You could also be portable and use an OUTER JOIN :

select t1.year, t1.x, t1.y
  from my_data t1
  left join my_data t2
    on t2.x = t1.x
   and t2.year > t1.year
 where t2.x is null

Solution 4

It's a lot simpler than the other solutions:

SELECT x, max(year), MAX(y) KEEP (DENSE_RANK FIRST ORDER BY year DESC)
  FROM table
  GROUP BY x

Solution 5

You can use common table expression (CTE), works also with duplicated rows (if required) Execution plan is the same, more or less

;With my_data_cte as (
    SELECT [year], x,y,ROW_NUMBER() OVER (
        PARTITION BY x
        ORDER BY [year] desc) as rn
FROM [dbo].[my_data])
select [year], x,y from my_data_cte 
where rn = 1

View more solutions

52,747

Author by

Stef Heyenrath

Updated on December 10, 2020

Comments

Stef Heyenrath over 3 years
I've the following table (my_data):
```
year |  X  |  Y
-----+-----+-----
2010 |  A  |  10
2011 |  A  |  20
2011 |  B  |  99
2009 |  C  |  30
2010 |  C  |  40
```
what is the best / smallest SQL statement to retrieve only the data related to the highest year and grouped by 'X' , like this:
```
year |  X  |  Y
-----+-----+-----
2011 |  A  |  20
2011 |  B  |  99
2010 |  C  |  40
```
Note that this result table will be used in a join.
Frank Schmitt almost 13 years

But this will not return the value for 'C' and 2010, as requested by the OP.
Lukas Eder almost 13 years

That won't produce the desired result
Stef Heyenrath almost 13 years

Thanks for this answer, it works correct. However I did accept the 'shurik' for the answer because it's a little bit more easy to understand.
Lukas Eder almost 13 years

Yes, that's quite an elegant way to do this
minglotus almost 9 years

@Nikolay It seems that you add highlight to the original code. right? Thanks
MT0 almost 9 years

Why LEFT JOIN rather than INNER JOIN? Why WHERE 1=1? Also using a join is less efficient than using MAX(...) KEEP( DENSE_RANK ... ).
Yordan Georgiev almost 9 years

txn for the correction => fixed to inner join + added the where for dev user friendliness ...
zloctb over 8 years

stackoverflow.com/questions/2854257/…
Toby Speight almost 7 years

Thank you for this code snippet, which may provide some immediate help. A proper explanation would greatly improve its educational value by showing why this is a good solution to the problem, and would make it more useful to future readers with similar, but not identical, questions. Please edit your answer to add explanation, and give an indication of what limitations and assumptions apply.