Using ROWNUMBER() to sort views!

Had a bit of an epiphany today when creating a view.

I needed to be able to join the view back on itself to create a running total column. In order to do this I thought I would use ROWNUMBER() to create a key and join on values less than that key.

In doing so I realised that ROWNUMBER() allows you to create a sorted view. This is something that anyone who has tried to create a sorted view will know can not be done unless you use a SELECT TOP statement, which is cludgey.

Using a " SELECT ROW_NUMBER() OVER(ORDER BY field1, field2, field3) AS 'ROWNUMBER', field1, field2, field3 ... FROM ... WHERE ... " statement however has the effect of sorting the values and you can use this to create a sorted view.

i.e.

CREATE VIEW vwTestView AS

SELECT ROW_NUMBER() OVER(ORDER BY field1, field2, field3) AS 'ROWNUMBER', field1, field2, field3 ... FROM ...

WHERE ...

Obviously this is only good if you are able to put the field in the view without upsetting anybody. But if you can it works well.