如何根据Row_Number Over Partition by column从Row_Number Over Part

Question

如何根据Row_Number Over Partition by列从Row_Number Over Partition by表中选择每个数据集?

请看下面的图表:

+-----------+-------------+-------------------+------------+----------+
| packageid | packagename | package max units | references | row_Numb |
+-----------+-------------+-------------------+------------+----------+
|     44    | Basic       | 10                | 103        | 1        |
|     45    | Basic       | 10                | 103        | 2        |
|     42    | Cola        | 10                | 102        | 1        |
|     43    | Cola        | 10                | 102        | 2        |
|     46    | Cola        | 10                | 102        | 3        |
|     2     | Home        | 11                | 101        | 1        |
|     11    | Home        | 11                | 101        | 2        |
|     21    | Home        | 11                | 101        | 3        |
|     1     | Spicy       | 11                | 104        | 1        |
|     3     | Spicy       | 11                | 104        | 2        |
|     41    | Spicy       | 11                | 104        | 3        |
+-----------+-------------+-------------------+------------+----------+

我想根据row_num列选择每个组中的每个数据集。

Answer 1

虽然听起来您已经有了ROW_NUMBER()列，但我相信这是您所要求的。对于每个PACKAGENAME的第一条记录，使用:

 SELECT s.* FROM (
     SELECT t.*,
            ROW_NUMBER() OVER(PARTITION BY t.packagename ORDER BY t.packageid) as rnk
     FROM YourTable t) s
WHERE s.rnk = 1

只使用内部查询

Answer 2

这是cte版本，如果你想从每个组中获取单个记录。

  ;with cte_1
   as(
     SELECT *,
            ROW_NUMBER() OVER(PARTITION BY [packagename],[package max units],  [references] ORDER BY [packageid]) as row_Numb 
     FROM YourTable ) 
   SELECT [packageid],[packagename],[package max units],[reference] 
   FROM cte_1
   WHERE row_Numb  = 1

Answer 3

您可以使用排序为ROW_NUMBER()的TOP 1 WITH TIES:

SELECT TOP 1 WITH TIES *
FROM YourTable
ORDER BY ROW_NUMBER() OVER (PARTITION BY packagename ORDER BY packageid)

输出:

packageid   packagename package max units   references
44          Basic       10                  103
42          Cola        10                  102
2           Home        11                  101
1           Spicy       11                  104