Posted by John Bell on 12/27/05 15:58

Hi

I am not sure what you are trying to attain as using something that is not
persisted will give you different results each time you query it e.g.

CREATE VIEW MYOBJECTS AS
SELECT NEWID() AS guid, * FROM SYSOBJECTS

SELECT * FROM MYOBJECTS

SELECT * FROM MYOBJECTS

If you want to rank the items then you can use a subquery e.g.

CREATE VIEW MyRanking AS

SELECT (SELECT COUNT(*) FROM SYSOBJECTS S WHERE S.ID <= O.ID) AS Rank, O.*
FROM SYSOBJECTS O

This would require the table you are using to have a method of uniquely
identifying each row.

John



"cmm" <cmourik@hotmail.com> wrote in message
news:1135691191.311510.143250@g43g2000cwa.googlegroups.com...
>I am trying to create a view in MSSQL that has a column which is an
> auto increment (or unique id) field but it doesn't work the way I want
> it. Does anybody have an idea how to do this? The example below gives
> me only 0 in the id field.
>
> CREATE VIEW dbo.tree_x_categories
> AS
> SELECT @@ROWCOUNTas id, *
> FROM <dbname>.dbo.tree_x_categories
> GO
>
> What I want is the fields from the table tree_x_categories plus an
> additional field that has a unique identifier.
>
> Many thnx,
>
> Chris
>

[Back to original message]