Click here to Skip to main content
15,399,440 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables that are, for the purpose of this question have the following columns:

SQL
Source:
Id,
Order

Target:
Id,
SourceId,
Order


I want to compare the order of all the rows, in the real data we're around 1m so by eye is no good.

The order on Source can have gaps and is mostly 1 based, the order on Target is 0 based with no gaps.

I want a query to find where the ordinal position of source and target are different. I don't mind that the values are different, just their position relative to the other rows.

What's the where clause?
SQL
SELECT s.Id
 ,s.Order
 ,t.Id
 ,t.Order
FROM Source s
JOIN Target t
  ON t.SourceId = s.Id
WHERE [Help Me Ronda]


What I have tried:

I tried just order not equal and source roder <> target order -1, but there are just too many gaps for this to work.

I can't change the values in the DB.
Posted
Updated 23-Sep-21 1:22am

How about a JOIN by row number?
Something like:
SQL
SELECT ... 
FROM (SELECT Id, SourceID, [Order], ROW_NUMBER () OVER (ORDER BY [Order] ASC) AS RN FROM Target) t
JOIN (SELECT Id, [Order], ROW_NUMBER () OVER (ORDER BY [Order] ASC) AS RN FROM Source) s
ON t.ID = s.ID AND t.RN != s.RN
   
Comments
Richard Deeming 23-Sep-21 7:23am
   
Snap! :)
OriginalGriff 23-Sep-21 8:07am
   
Great minds and all that! :D
Nagy Vilmos 24-Sep-21 3:50am
   
Noice, Richard's cursors made it easier.

tbf, I didn't know about ROW_NUMBER(), that was the missing function what I needed.
OriginalGriff 24-Sep-21 4:02am
   
Elephant me! I didn't even notice it was you Nagy - you've been so quiet the last couple of years ...
How about:
SQL
WITH cteSource As
(
    SELECT
        Id,
        Order,
        ROW_NUMBER() OVER (ORDER BY Order) As RN
    FROM
        Source
),
cteTarget As
(
    SELECT
        Id,
        SourceId,
        Order,
        ROW_NUMBER() OVER (PARTITION BY SourceId ORDER BY Order) As RN
    FROM
        Target
)
SELECT
    S.Id,
    S.Order,
    S.RN,
    T.Id,
    T.Order,
    T.RN
FROM
    cteSource As S
    INNER JOIN cteTarget As T
    ON T.SourceId = S.Id
WHERE
    S.RN != T.RN
;
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
Comments
Nagy Vilmos 24-Sep-21 3:49am
   
This did it. Sadly I still have around 24k rows with errors. :(

But that's my problem now.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900