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


row_number() and order by on column not in results

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

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 ASC) as my_id, .....
  FROM table1, table2
 WHERE = and ....

but 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 to

row_number() and order by on column not in results

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:

    row_number() OVER(ORDER BY ASC) as my_id


Then JOIN to this table while splitting the data:

SELECT t.my_id, ...
FROM table1 t1
INNER JOIN table2 t2
    ON =
    ON =

No matter what you do, that INNER JOIN from to 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