CODEWITHSUNDEEP
sqlsql-servert-sql
vibs
vibs

Reputation: 145

Check two table's records are in the same order

I have two recordset (temp table data) with some columns. I need to check that both table's records are in the same order.

I am not checking differences between two recordset or common rows. I need to check that they are in the same order.(both tables have records order by some columns already and need to check order of both tables are same using GUID column)

If Guid matches then I will insert information in some table and if not then into log table, but it should move to/compare next record in both cases.

I am thinking to nested loop for both temp tables and check the order by comparing Guid columns.

Any other approach?

Upvotes: 0

Views: 966

Answers (3)

Sahi
Sahi

Reputation: 1484

You can generate row numbers (without ordering) and check for the GUIDs having different row numbers.

    declare @table1 table(id varchar(MAX))
    declare @table2 table(id VARCHAR(MAX))

    insert into @table1
    select '653E6A93'
    union all
    select '5461F417'
    union all
    select '330526D6'

    insert into @table2
    select '653E6A93'
    union all
    select '330526D6'
    union all
    select '5461F417'


    ;with cte1
    AS
    (
        select *, ROW_NUMBER() OVER (ORDER BY (SELECT null)) AS rn from @table1
    )
    ,

    cte2
    AS
    (
        select *, row_number() OVER(order by (SELECT NULL)) rn from @table2
    )

    select c1.id from cte1 c1
    JOIN cte2 c2 on c1.id=c2.id and c1.rn<>c2.rn

Upvotes: 0

SQLHelp
SQLHelp

Reputation: 41

Not sure if I got your question right but below is what I think you should do.

The below is from your question "If Guid matches then I will insert information in some table and if not then into log table, but it should move to/compare next record in both cases."

You need 2 insert statements

In the first one, do an inner join on GUIDs and insert the result to table1.

In the second query, do the left join and filter it by null and then insert the result set in to the log table.

insert into sometable
select * 
from Table1 t1
inner join Table2 t2 on T1.GUID = T2.GUID

insert into logtable
select * 
from Table1 t1
left join Table2 t2 on T1.GUID = T2.GUID
where t2.guid is null

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your question is not very clear. Just some hard facts:

  • There is no implicit order! You can fill your table in a given order and the next SELECT might return the data exactly in this order - but this is random! You should never rely on a sort order! There is none!
  • The only guaranteed way to enforce a sort order is to use ORDER BY on the outermost query.
  • One specialty might be the usage of sorting functions like ROW_NUMBER(). But this is to broad to discuss this here.

If I get you correctly, you need to check for rows existing on both sides, if they appear in the same order. Try this:

DECLARE @t1 TABLE(YourGuid UNIQUEIDENTIFIER, Descr VARCHAR(100),SomeSortableColumn DATETIME);
INSERT INTO @t1 VALUES('653E6A93-3EBA-4D5E-A8F3-C36462A55FEF','Row 1',{d'2018-01-01'})
                     ,('5461F417-1D14-4CFE-822D-3F028492F839','Row 2',{d'2018-01-02'})
                     ,('E9BDE8C6-237A-49F6-88BD-9EB211FB12F2','Row 3',{d'2018-01-03'})
                     ,('64343D33-8AD2-475F-AC27-66A6BFD011C9','Row 4',{d'2018-01-04'})
                     ,('5778229D-B20E-41FC-9A2E-8694B204E4D3','Row 5',{d'2018-01-05'})
                     ,('9AC0BB10-0F70-488C-A249-45A3C688D877','Row 6',{d'2018-01-06'})
                     ,('330526D6-B931-4CEA-BB03-30F3783E6284','Row 7',{d'2018-01-07'})
                     ,('6F68F260-2F64-4C78-9DA5-20E0FF22B4A1','Row 8',{d'2018-01-08'})
                     ,('E09090F1-FC85-41EE-819B-8275A22BD075','Row 9',{d'2018-01-09'});

DECLARE @t2 TABLE(YourGuid UNIQUEIDENTIFIER, Descr VARCHAR(100),SomeSortableColumn DATETIME);
INSERT INTO @t2 VALUES('653E6A93-3EBA-4D5E-A8F3-C36462A55FEF','Row 1',{d'2018-01-01'})
                     ,('5461F417-1D14-4CFE-822D-3F028492F839','Row 2',{d'2018-01-02'})
                     --missing in 2: 3 & 4
                     ,('5778229D-B20E-41FC-9A2E-8694B204E4D3','Row 5',{d'2018-01-05'})
                     --other GUID  
                     ,(NEWID(),'Row 6',{d'2018-01-06'})
                     ,('330526D6-B931-4CEA-BB03-30F3783E6284','Row 7',{d'2018-01-07'})
                     --other date
                     ,('6F68F260-2F64-4C78-9DA5-20E0FF22B4A1','Row 8',{d'2018-01-01'})
                     ,('E09090F1-FC85-41EE-819B-8275A22BD075','Row 9',{d'2018-01-09'})
                     --missing in 1
                     ,(NEWID(),'Other row',{d'2018-01-03'})
;

--This query uses an INNER JOIN on the GUID column to omit rows, which do not exist in both sets. And it uses two times ROW_NUMBER(), each call sorted after the same column, but taken from different sources. The result shows rows where these indexes are different. 

WITH ColumnsToCompare AS
(
    SELECT t1.YourGuid
          ,t1.Descr AS Descr1
          ,t2.Descr AS Descr2
          ,t1.SomeSortableColumn AS Sort1
          ,t2.SomeSortableColumn AS Sort2
          ,ROW_NUMBER() OVER(ORDER BY t1.SomeSortableColumn) AS Index1
          ,ROW_NUMBER() OVER(ORDER BY t2.SomeSortableColumn) AS Index2
    FROM @t1 AS t1
    INNER JOIN @t2 AS t2 ON t1.YourGuid =t2.YourGuid 
)
SELECT *
FROM ColumnsToCompare
WHERE Index1<>Index2

Upvotes: 1

Related Questions