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