views:

35

answers:

1

I have the following

Chars

A
C
W
B
J
M

How can I insert some sequential numbers so that after insertion of the numbers the order of characters will not change?

I mean if I use row_number(), the output Character order is changing like

select 
ROW_NUMBER() over(order by chars) as id,
t.* from @t t

Output:

id chars

1   A
2   B
3   C
4   J
5   M
6   W

My desired expectation is

id chars

1   A
2   C
3   W
4   B
5   J
6   M

Also, I cannot use any identity field like id int identity because I am in the middle of a query and I need to maintain a inner join for achieving something.

I hope I do make myself clear.

Please help. Thanks in advance

+5  A: 

There is no implicit ordering of rows in SQL. If some ordering is desired, be it order in which items were inserted or any other order, it must be supported by a user-defined column.

In other words, the SQL standard doesn't require the SQL implementations to maintain any order. On the other hand the ORDER BY clause in a SELECT statement can be used to specify the desired order, but such ordering is supported by the values in a particular (again, user defined) column.

This user defined column may well be an auto-incremented column for which SQL assigns incremental (or otherwise) values to, and this may be what you need.

Maybe something like...

CREATE TABLE myTable
(
   InsertID smallint IDENTITY(1,1),
   OneChar  CHAR(1),
   SomeOtherField VARCHAR(20)
   -- ... etc.
)

INSERT INTO myTable (OneChar, SomeOtherField) VALUES ('A', 'Alpha')
INSERT INTO myTable (OneChar, SomeOtherField) VALUES ('W', 'Whiskey')
INSERT INTO myTable (OneChar, SomeOtherField) VALUES ('B', 'Bravo')
-- ... etc.

SELECT OneChar
FROM myTable
ORDER BY InsertId

'A'
'W'
'B'
--...
mjv
Hi, infact that is what I said that the result what I have presented here is an outcome of some query. I don't have a table as such where those data are present. Henceforth, I cannot use identity column.
priyanka.sarkar
You can use something like `SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) AS id, t.* FROM @t t` This is a trick, for GetDate() only gets evaluated once anyway, and shouldn't change the implied order of this @t thing... But as said if this order hasn't been explicitly required you shouldn't rely on it. t.* from @t t
mjv
thanks man... even i was thinking to do like select 1 as rn,col from table and then applyinh row_number(over rn) to achieve the same.
priyanka.sarkar