Sql to find different order

Question

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 22-Sep-21 23:24pm

Nagy Vilmos

Updated 23-Sep-21 1:22am

Add a Solution

2 solutions

Solution 1

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

Posted 23-Sep-21 1:19am

OriginalGriff

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2021-09-23T01:22:00

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[^]