Reputation: 1700
The following query results in duplicate row_numbers after the first partitioned set. For example, the first set of fldPK = 2403 and fldCIA = 1 I receive the following...
2403 1 1
2403 1 2
2403 1 3
2403 1 4
and in the second set I receive...
2403 2 1
2403 2 1 <-- duplicate 1 within the same partition
2403 2 2
2403 2 3
SELECT *, 1 AS fldBF,
ROW_NUMBER() OVER (PARTITION BY fldPK, fldCIA ORDER BY fldPK) AS sCount
INTO #tmpTable
FROM V_qryCSPGA
WHERE fldPK IN(SELECT DISTINCT thePK FROM FN_qryAllDTPK())
UNION ALL
SELECT *, 1 AS fldBF,
ROW_NUMBER() OVER (PARTITION BY fldPK, fldCIA ORDER BY fldPK) AS sCount
FROM FN_qryCSGBA()
WHERE fldPK IN(SELECT DISTINCT thePK FROM FN_qryAllDTPK())
ORDER BY fldPK, fldCIA, fldNDat;
Why is this occurring? for every set of fldPK and fldCIA I need a to restart the row number count at 1, more proper data would be...
2403 3 1
2403 3 2
2403 3 3
2403 3 4
2403 3 5
2403 3 6
2403 3 7
2403 3 8
2403 3 9
Upvotes: 0
Views: 3888
Reputation: 115630
This will stop the duplicate numbers. Whether the new numbering will be what you are really after, is another thing:
; WITH cte AS
( SELECT *, 1 AS fldBF
FROM V_qryCSPGA
WHERE fldPK IN(SELECT DISTINCT thePK FROM FN_qryAllDTPK())
UNION ALL
SELECT *, 1 AS fldBF
FROM FN_qryCSGBA()
WHERE fldPK IN(SELECT DISTINCT thePK FROM FN_qryAllDTPK())
)
SELECT *,
ROW_NUMBER() OVER (PARTITION BY fldPK, fldCIA ORDER BY fldPK) AS sCount
INTO #tmpTable
FROM cte
ORDER BY fldPK, fldCIA, fldNDat;
Upvotes: 3