MSSQL Compare two tables row by row

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

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;

Advertisement

Answer