CODEWITHSUNDEEP
sqlsql-servert-sql
whytheq
whytheq

Reputation: 35577

possible to add a row number to a SELECT clause that is not order dependent

If I have this fiddle

CREATE TABLE temp
(
y          char(9),
x          char(9)
);

insert into temp (x, y) values ('j', 'hello');
insert into temp (x, y) values ('j', 'world');
insert into temp (x, y) values ('q', 'foo');
insert into temp (x, y) values ('q', 'bar');

select 
x
,y
from temp

I'd like to have a row number included in the SELECT clause. So the result would look like this:

x y     r
j hello 1 
j world 2
q foo   3
q bar   4

I don't believe Row_number() over will work as this requires an Order and I do not want to change the order from the SELECT * FROM. Also do not want to an an identity column to the client db table

Is this possible in SQL-Server?

Upvotes: 0

Views: 616

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

If you're okay with r being non-deterministic (in other words it may not work the same every time):

select 
x
,y
, r = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
from temp
order by r;

To see even a very simple case where it doesn't work the same way:

CREATE TABLE temp
(
y          char(9) PRIMARY KEY,
x          char(9)
);

And of course if you want the order to be random:

select 
x
,y
, r = ROW_NUMBER() OVER (ORDER BY NEWID())
from temp
order by r;

Upvotes: 3

Related Questions