I am trying compare the results of two select statements row by row. So for example the table looks like the following:
GUID | NUMBER (auto inc) | VERSION (timestamp) | Name
A | 4 | 1 | Robert
B | 9 | 2 | John
C | 14 | 3 | Magret
Now the selects look like
select guid from table order by number;
select guid from table order by version;
Now the tables should be compared row by row, if the GUIDs are the same in both selects at the same line. If so, return true, else false.
edit: The goal is to detect a change of the columns GUID and NAME. The column NUMBER is an auto increment value, that only is increased when the entry is created. The VERSION is a timestamp, that changes every time the entry has been updated. Now it is the goal the compare both select queries from above to determine a change it the table, because if the order of the entries is different then a change has occurred.
Thanks, shirocko
Advertisement
Answer
If you want to know if all rows are ordered the same with the two columns, then:
select (case when count(*) = sum(case when rnk_1 = rnk2 then 1 else 0 end)
then 'All Same'
else 'Different'
end)
from (select t.*,
rank() over (order by number) as rnk_1,
rank() over (order by version) as rnk_2
from t
) t;