Question: row_number() and order by on column not in results

Question

row_number() and order by on column not in results

Answers 1
Added at 2016-12-29 00:12
Tags
Question

I have a case where I would like to use row_number() is SQL Server to get back unique numbers in two related queries on the same table. The problem is that I don't have a column which appears in both queries which I can rely upon to be unique, although both queries run against the same table with a single column primary key (which I'll call pk here) and the same WHERE clause. So both queries are in the form

SELECT row_number() OVER(ORDER BY table1.pk ASC) as my_id, .....
  FROM table1, table2
 WHERE table1.pk = table2.fk and ....

but table1.pk only appears in the result set of one of these queries. Can I rely on both of these queries returning the rows in the same order (relative to the rows in table1)? If not, can someone suggest another approach? These SELECT are the input to INSERT statements, so I don't have a lot of leeway as to the result columns.

Answers
nr: #1 dodano: 2016-12-29 01:12

If I have any questions about whether or not the order will be the same, I use an intermediate table binding the row_number() value to the pk. A simple example would be:

INSERT INTO #tmpPK
SELECT table1.pk,
    row_number() OVER(ORDER BY table1.pk ASC) as my_id

CREATE UNIQUE INDEX ix1 ON #tmpPK (pk) INCLUDE (my_id)

Then JOIN to this table while splitting the data:

SELECT t.my_id, ...
FROM table1 t1
INNER JOIN table2 t2
    ON t1.pk = t2.fk
...
INNER JOIN #tmpPK t
    ON t1.pk = t.pk

No matter what you do, that INNER JOIN from table1.pk to #tmpPK.pk ensures that the my_id value will be the same for the different PKs.

That said, unless there are rows missing from one table you are splitting to that might be added to the other table you are splitting to, I doubt you will have a problem.

Source Show
◀ Wstecz