RowNum() and Order by

[Gruuka]

My colleague made an sql query where he uses Rownum to sort the selection with, now his query is setup like the following:

Select *,RowNum
From
    (select rownum() over(id) from test) as RowNum
where
--
order by RowNum

The thing that is happening in this code is that a subtable is selected with a rownum in it's result so it can be sorted on it. But the odd thing is when the final order by in the where clause is left out the sorting is in a random order
if the orber by RowNum is there it orders on that result as normal.

 

[Jamfool]

CREATE TABLE TEST
(
id INT,
something VARCHAR(10)
)
GO
INSERT INTO test VALUES (2,'mouse')
INSERT INTO test VALUES (235,'cat')
INSERT INTO test VALUES (2234,'dog')
INSERT INTO test VALUES (43,'monkey')
INSERT INTO test VALUES (46364,'giraffe')
GO
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS RowNum FROM TEST
GO
DROP TABLE TEST