I have two tables, t1, t2.
For instance: t1 is like this:
ID Ordinal
1 1
2 2
3 3
4 4
t2 is like this:
ID Ordinal
1 1
开发者_运维技巧2 3
3 5
4 6
In this scenario, while I do the comparison of the ordinal, I want to return the order as "no Change", because if you look carefully, the actual order of those two tables are not changed.
but in a scenario like this: t2 is like this:
ID Ordinal
1 1
2 5
3 3
4 6
I would like to see the result as ID 2 and 3 are changed from T1.
Any thoughts?
Thanks!
- List item
Try this:
SELECT T2.ID
FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY Ordinal) AS rn FROM T1) T1
JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY Ordinal) AS rn FROM T2) T2
ON T1.ID = T2.ID
WHERE T1.rn <> T2.rn
Try using the query below:
SELECT a.ID, a.Ordinal, b.Ordinal
FROM (
SELECT t1.*, rownum rnm
FROM t1
ORDER BY Ordinal
)a,
(
SELECT t2.*, rownum rnm
FROM t2
ORDER BY Ordinal
)b
WHERE a.ID = b.ID
-- AND a.rnm <> b.rnm //This deosn't work
AND a.Ordinal <> b.Ordinal
Edit: Updated the query to return the required result set
精彩评论