counting records in a view

I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.
Is this possible using a view?I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.

Is this possible using a view?

One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott|||One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott

Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

Thanks again,

Steve|||How many rows are we talking about?

How about the DDL for the table|||One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott

Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

Thanks again,

Steve|||Yes, you can do this in any version of sql server as long as you have a unique column (or columns) that you can order by.
select MyTable.*,
(select count(*)
from MyTable SubTable
where SubTable.SortColumn <= MyTable.SortColumn) as OrdinalValue
from MyTable