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

Question

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.

I forgot to include de information that the data should be grouped by the 'X' column. — Stef Heyenrath, Jun 01 '11 at 08:37

score 28 · Accepted Answer · answered Jun 01 '11 at 08:40

28

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

answered Jun 01 '11 at 08:40

schurik

7,798
2
23
29

Yes, that's quite an elegant way to do this – Lukas Eder Jun 01 '11 at 09:03
http://stackoverflow.com/questions/2854257/sql-how-to-select-a-row-having-a-column-with-max-value – zloctb Oct 11 '15 at 10:31

score 14 · Answer 2 · answered Jun 01 '11 at 08:35

14

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

answered Jun 01 '11 at 08:35

Frank Schmitt

30,195
12
73
107

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. – Stef Heyenrath Jun 01 '11 at 08:53

score 4 · Answer 3 · answered Jun 01 '11 at 09:02

4

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

answered Jun 01 '11 at 09:02

Benoit

76,634
23
210
236

score 4 · Answer 4 · answered Jun 01 '11 at 09:35

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

answered Jun 01 '11 at 09:35

Gary Myers

34,963
3
49
74

score 2 · Answer 5 · edited Oct 20 '17 at 13:44

2

select year, x, y 
 from my_data stable 
where stable.year = (select max(year) 
                     from my_data tables 
                     where tables.x = stable.x);

edited Oct 20 '17 at 13:44

Frank Schmitt

30,195
12
73
107

answered Jun 16 '15 at 10:12

minglotus

83
6

@Nikolay It seems that you add highlight to the original code. right? Thanks – minglotus Jun 17 '15 at 11:55

Artyom · Answer 6 · 2015-07-08T11:02:50.223

2

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

edited Jul 08 '15 at 11:02

answered Jul 08 '15 at 08:17

Artyom

21
2

score 1 · Answer 7 · answered Sep 22 '14 at 13:40

Gary Myers, your solution does not work, if, for example, for value A, year is smaller than 2010 and that year has maximum value. (FOR example, if row 2005,A,50 existed) In order to get correct solution, use the following. (which just swaps values)

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

score 0 · Answer 8 · edited Dec 09 '20 at 15:36

0

-- I had a slightly different case and just wandering why this one should't work 
SELECT my_data.x , my_data.y , my_data1.max_year 
FROM my_data
INNER JOIN 
( 
  SELECT x , max (year ) as max_year
  FROM my_data
  -- WHERE 1=1
  -- AND FILTER1=VALUE1
  GROUP BY my_data.x
) my_data1
ON ( my_data.x = my_data1.x )

edited Dec 09 '20 at 15:36

Stevoisiak

23,794
27
122
225

answered Jul 23 '15 at 09:04

Yordan Georgiev

5,114
1
56
53

Why `LEFT JOIN` rather than `INNER JOIN`? Why `WHERE 1=1`? Also using a join is less efficient than using `MAX(...) KEEP( DENSE_RANK ... )`. – MT0 Jul 23 '15 at 09:14
txn for the correction => fixed to inner join + added the where for dev user friendliness ... – Yordan Georgiev Jul 23 '15 at 10:48

Stevoisiak · Answer 9 · 2020-12-09T16:39:03.080

You can select the most recent year for each X by using a subquery in your criteria:

select a.year, a.x, a.y
from my_data a
where
  a.year = (
    select max(a_yr.year) from my_data a_yr
    where a_yr.x = a.x);

Data:

year |  X  |  Y
-----+-----+-----
2010 |  A  |  10
2011 |  A  |  20
2011 |  B  |  99
2009 |  C  |  30
2010 |  C  |  40

Results:

year |  X  |  Y  
-----+-----+-----
2011 |  A  |  20 
2011 |  B  |  99 
2010 |  C  |  40

Based on my limited testing, this method seems to be faster than using partition by.

score -3 · Answer 10 · answered Aug 02 '17 at 09:36

-3

this can also be the solution

select greatest( (e),(g),(c),(a),(b) ) as latestdate from abc

answered Aug 02 '17 at 09:36

Amardeep Bhagat

1

Thank you for this code snippet, which may provide some immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) 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. – Toby Speight Aug 02 '17 at 09:58

score -4 · Answer 11 · answered Jun 01 '11 at 08:36

-4

The simplest is

Select * 
from table 
where year = (select max(year) from table)

It will possibly cause a table scan unless there is an index on year. But with an index should be performant

answered Jun 01 '11 at 08:36

Karl

3,312
21
27

1

But this will not return the value for 'C' and 2010, as requested by the OP. – Frank Schmitt Jun 01 '11 at 08:39
That won't produce the desired result – Lukas Eder Jun 01 '11 at 08:39

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

11 Answers11

Linked