如何在T-SQL中按一个列进行分组并按另一个列进行排序

Question

我有一个包含一些值的项目表，其中包括成本和购买日期。我想要获取每种项目类型中最昂贵的一个项目列表，并按特定项目的购买日期对其进行排序，但结果中不包含购买日期。

我的表格（简化版）：

CREATE TABLE Purchases
    (ItemType varchar(25),
    Cost int,
    PurchaseDate smalldatetime)

以下是我的样本数据：

INSERT INTO Purchases VALUES
    ('Hat',     0,      '2007-05-20 15:22'),
    ('Hat',     0,      '2007-07-01 15:00'),
    ('Shirt',   3500,   '2007-07-30 08:43'),
    ('Pants',   2000,   '2008-07-30 12:00'),
    ('Pants',   4000,   '2009-03-15 07:30'),
    ('Sweater', 3000,   '2011-05-20 15:22'),
    ('Sweater', 3750,   '2012-07-01 22:00'),
    ('Sweater', 2700,   '2014-06-12 11:00'),
    ('Hat',     4700,   '2015-06-29 07:10')

我期望的输出结果（为了更清晰，加上日期）：

ItemType                MostExpensivePerType
------------------------- --------------------
Shirt                     3500                (2007-07-30 08:43)
Pants                     4000                (2009-03-15 07:30)
Sweater                   3750                (2012-07-01 22:00)
Hat                       4700                (2015-06-29 07:10)

我的工作成果：

我已经尝试了各种方法，目前最好的结果是使用以下查询语句：

SELECT 
    ItemType, MAX(Cost) AS MostExpensivePerType 
FROM 
    Purchases 
GROUP BY 
    ItemType 
ORDER BY 
    MostExpensivePerType DESC

该查询可以按成本排序，每个项目类型的最昂贵的物品排在前面。如果没有ORDER BY子句，它们似乎是按字母顺序排序的。我意识到我的查询还需要日期列，但我能否输入并将其“隐藏”在结果中？或者我需要将到目前为止的结果保存在临时表中，并与我的常规表连接？最好的方法是什么？ SQL Fiddle在此！

- Tobbe

---

我喜欢你在问题中添加输入数据和期望输出的方式。但是我并没有完全理解你的问题。你想要什么样的最终输出？ - Sateesh Pagolu

我想要每个类别中最昂贵的物品列表：最昂贵的帽子、衬衫等（每种物品只列举一个），但我希望按照购买日期对列表进行排序。因此，即使最昂贵的帽子比最昂贵的衬衫更昂贵，如果衬衫是在帽子之前购买的，则应该先列出衬衫。请参见马克·班尼斯特的答案 :) - Tobbe

其实，我刚意识到我误解了问题 - 我认为你想按类别中任何商品的最近购买情况排序，但现在我意识到你想按类别中最昂贵的商品的购买日期排序。我建议取消接受我的答案并接受戈登的答案。 - user359040

好的，我取消了你的答案。戈登的答案对我没用，但我相信我们会想出解决办法的 :) - Tobbe

Answer 1

使用窗口函数：

select ItemType, Cost MostExpensivePerType
from (select p.*,
             row_number() over (partition by itemtype order by cost desc) as seqnum
      from purchases p
     ) t
where seqnum = 1
order by PurchaseDate;

请点击这里访问SQLFiddle。

- Gordon Linoff

---

这可能是我做错了什么，但我收到了以下错误信息：Msg 4104，Level 16，State 1，Line 23 无法绑定多部分标识符“p.ItemType”。 Msg 207，Level 16，State 1，Line 23 无效的列名“MostExpensivePerType”。 - Tobbe

@Tobbe：我已经更新了Gordon的答案以解决这个问题，并添加了一个SQLFiddle链接。 - user359040

Answer 2

SELECT 
  ItemType, 
  MAX(Cost) AS MostExpensivePerType
FROM 
  Purchases 
GROUP BY 
  ItemType 
ORDER BY
  Max(PurchaseDate) DESC

- Ralf de Kleine

---

可能是我的数据库软件的问题，但我收到了这个错误：在视图、内联函数、派生表、子查询和公共表达式中，ORDER BY子句无效，除非也指定了TOP、OFFSET或FOR XML。 - Tobbe

@Tobbe 你可能是在不同的上下文中使用了这个代码片段（视图/子查询/CTE/..）。 - CervEd

Answer 3

SELECT a.ItemType,a.MostExpensivePerType 
FROM (
  SELECT ItemType, MAX(Cost) AS MostExpensivePerType , MAX(PurchaseDate) AS MaxPurchaseDate 
  FROM Purchases 
  GROUP BY ItemType
  ) a
  ORDER BY a.MaxPurchaseDate DESC

- Daniel Stackenland