Как использовать "Partition By" или "Max"?

У меня есть следующая таблица (my_data):

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

что является лучшим/самым маленьким оператором SQL для извлечения только данных, относящихся к самому высокому году и сгруппированных по "X", например:

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

Обратите внимание, что эта таблица результатов будет использоваться в соединении.

Ответ 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

Ответ 2

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

Ответ 3

Вы также можете быть переносимыми и использовать 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

Ответ 4

Это намного проще, чем другие решения:

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

Ответ 5

Гэри Майерс, ваше решение не работает, если, например, для значения A, год меньше 2010 года и этот год имеет максимальное значение. (Например, если строка 2005, A, 50 существовала) Чтобы получить правильное решение, используйте следующее. (который просто меняет значения)

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

Ответ 6

Вы можете использовать общее табличное выражение (CTE), работает также с дублируемыми строками (если требуется) План выполнения тот же, более или менее

;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

Ответ 7

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

Ответ 8

  -- 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 )

Ответ 9

это также может быть решением

выберите наибольший ((e), (g), (c), (a), (b)) как lastdate из abc

Ответ 10

Простейшим является

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

Это может привести к сканированию таблицы, если нет индекса в год. Но с индексом должно быть выполнено