Search code examples
sql-serversql-server-2008sql-order-bywindow-functionsrow-number

SQL Row_Number() (Partition by… Order By…) IGNORES Order statement


I couldn’t find a function to support what I’m trying to approach.

Let’s suppose we have the following table which contains a field sort order and certain duplicates

+----------+----------+-----+-------------+-----------+
| UniqueId |    Id    | Qty | RetailPrice | SortOrder |
+----------+----------+-----+-------------+-----------+
|     3124 | 92361725 |  25 |      269.99 |         1 |
|     2627 | 92361725 |  25 |      269.99 |         2 |
|     7635 | 92361725 |  25 |      269.99 |         3 |
|     8732 | 92361725 |  25 |      269.99 |         4 |
|     3791 | 92361725 |  20 |      269.99 |         5 |
|     4328 | 92361725 |  25 |      269.99 |         6 |
+----------+----------+-----+-------------+-----------+

I want to enumerate my rows increasing their value when a duplicate is found, if not then reset the row number. The result must be shown in the column rn the following table if Qty is the column to evaluate:

+----------+----------+-----+-------------+-----------+----+
| UniqueId |    Id    | Qty | RetailPrice | SortOrder | rn |
+----------+----------+-----+-------------+-----------+----+
|     3124 | 92361725 |  25 |      269.99 |         1 |  1 |
|     2627 | 92361725 |  25 |      269.99 |         2 |  2 |
|     7635 | 92361725 |  25 |      269.99 |         3 |  3 |
|     8732 | 92361725 |  25 |      269.99 |         4 |  4 |
|     3791 | 92361725 |  20 |      269.99 |         5 |  1 |
|     4328 | 92361725 |  25 |      269.99 |         6 |  1 |
+----------+----------+-----+-------------+-----------+----+

I tried to use ROW_NUMBER() function but I can’t get the results I want

;WITH Table1 AS(
SELECT 3124 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 1 SortOrder UNION ALL
SELECT 2627 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 2 SortOrder UNION ALL
SELECT 7635 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 3 SortOrder UNION ALL 
SELECT 8732 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 4 SortOrder UNION ALL
SELECT 3791 UniqueId,92361725 Id, 20 Qty, 269.99 RetailPrice, 5 SortOrder UNION ALL
SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 6 SortOrder
)

SELECT UniqueId, Id, Qty, RetailPrice, SortOrder,
ROW_NUMBER() OVER (PARTITION BY Qty ORDER BY SortOrder) rn
FROM Table1
+----------+----------+-----+-------------+-----------+----+
| UniqueId |    Id    | Qty | RetailPrice | SortOrder | rn |
+----------+----------+-----+-------------+-----------+----+
|     3791 | 92361725 |  20 |      269.99 |         5 |  1 |
|     3124 | 92361725 |  25 |      269.99 |         1 |  1 |
|     2627 | 92361725 |  25 |      269.99 |         2 |  2 |
|     7635 | 92361725 |  25 |      269.99 |         3 |  3 |
|     8732 | 92361725 |  25 |      269.99 |         4 |  4 |
|     4328 | 92361725 |  25 |      269.99 |         6 |  5 |
+----------+----------+-----+-------------+-----------+----+

The Order By is completely ignored, can anyone help?


Solution

  • Here you go. Since you are in 2008 I replicated Lead and Lag by self joining this table on SortOrder +/- 1. I also updated your sample set to account for new islands of Qty 25.

    Sorry for the wall of text but I had to update your sample set to add 2 rows to island 3 and create 2 CTEs to get the island ranges.

    --Updates Sample Set with 3 Islands.
    WITH Table1 AS(
    SELECT 3124 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 1 SortOrder UNION ALL --Island 1
    SELECT 2627 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 2 SortOrder UNION ALL --Island 1
    SELECT 7635 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 3 SortOrder UNION ALL --Island 1
    SELECT 8732 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 4 SortOrder UNION ALL --Island 1
    SELECT 3791 UniqueId,92361725 Id, 20 Qty, 269.99 RetailPrice, 5 SortOrder UNION ALL --Island 2
    SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 6 SortOrder UNION ALL --Island 3
    SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 7 SortOrder UNION ALL --Island 3
    SELECT 4328 UniqueId,92361725 Id, 25 Qty, 269.99 RetailPrice, 8 SortOrder           --Island 3
    ),
    
    --Creating a CTE to get the Lead and Lag since this is 2008. This will allow us to determine if a row is the first or last row of an island.
    LeadLagTable AS(
    SELECT
        Table1.UniqueId,
        Table1.Id,
        Table1.Qty,
        Table1.RetailPrice,
        Table1.SortOrder,
        LeadTable.SortOrder AS LeadSortOrder,
        LagTable.SortOrder AS LagSortOrder,
        CASE
            WHEN LagTable.SortOrder IS NULL THEN 1
            ELSE 0
            END AS StartRowFlag,
        CASE
            WHEN LeadTable.SortOrder IS NULL THEN 1
            ELSE 0
            END AS LastRowFlag
    FROM Table1
    LEFT JOIN Table1 LeadTable ON
        Table1.SortOrder = LeadTable.SortOrder - 1
        AND Table1.Qty = LeadTable.Qty
    LEFT JOIN Table1 LagTable ON
        Table1.SortOrder = LagTable.SortOrder + 1
        AND Table1.Qty = LagTable.Qty
    ),
    
    --With the LeadLagTable we can now get the ranges for each island, as well as a unique ID for each island.
    Ranges AS (
    SELECT
        RangeStart,
        RangeEnd,
        ROW_NUMBER() OVER (ORDER BY RangeStart) AS RangeRowNum
    FROM (
        SELECT
            StartRow.SortOrder AS RangeStart,
            EndRow.SortOrder RangeEnd,
            ROW_NUMBER() OVER (PARTITION BY StartRow.SortOrder ORDER BY EndRow.SortOrder) AS rn
        FROM LeadLagTable StartRow
        JOIN LeadLagTable EndRow ON
            StartRow.StartRowFlag = 1
            AND EndRow.LastRowFlag = 1
            AND StartRow.SortOrder <= EndRow.SortOrder
            AND StartRow.Qty = EndRow.Qty
        ) tbl
    WHERE rn = 1
    )
    

    And here is the actual Query.

    --We now join on the island ranges, and partition by the Island ID.
    SELECT
        UniqueId,
        Id,
        Qty,
        RetailPrice,
        SortOrder,
        ROW_NUMBER() OVER (PARTITION BY RangeRowNum ORDER BY SortOrder) AS rn
    FROM Table1
    LEFT JOIN Ranges ON
        Table1.SortOrder >= Ranges.RangeStart
        AND Table1.SortOrder <= Ranges.RangeEnd