Add auto number field to view

I have a simple view something like "SELECT * from <table_name> order
by field1,field2,field3
I have a program that needs to read these values in by the order I have
ordered them in the database however it needs an incremental numeric
value to do so. Is there any way to add to the view, a field that after
the order by is done, that places a numeric incremental value in
another virtual or real column?
Thanks.
JRhttp://www.aspfaq.com/2427
"JR" <jriker1@.yahoo.com> wrote in message
news:1149872089.709215.141860@.i39g2000cwa.googlegroups.com...
>I have a simple view something like "SELECT * from <table_name> order
> by field1,field2,field3
> I have a program that needs to read these values in by the order I have
> ordered them in the database however it needs an incremental numeric
> value to do so. Is there any way to add to the view, a field that after
> the order by is done, that places a numeric incremental value in
> another virtual or real column?
> Thanks.
> JR
>|||JR wrote:
> I have a simple view something like "SELECT * from <table_name> order
> by field1,field2,field3
> I have a program that needs to read these values in by the order I have
> ordered them in the database however it needs an incremental numeric
> value to do so. Is there any way to add to the view, a field that after
> the order by is done, that places a numeric incremental value in
> another virtual or real column?
> Thanks.
> JR
on 2005, use row_number()|||Using SQL Server 2000.
Alexander Kuznetsov wrote:
> JR wrote:
> on 2005, use row_number()|||I was looking at the rank option however with that it seems to want you
to define each and every column in your view in the group by clause.
Aaron Bertrand [SQL Server MVP] wrote:
> http://www.aspfaq.com/2427
>
>
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1149872089.709215.141860@.i39g2000cwa.googlegroups.com...|||>I was looking at the rank option however with that it seems to want you
> to define each and every column in your view in the group by clause.
Which you should be doing anyway; don't be lazy, it doesn't pay off in the
long run!
http://www.aspfaq.com/2096|||OK, I'll give it a try however I have over 50 columns so kind of a
pain.
Aaron Bertrand [SQL Server MVP] wrote:
> Which you should be doing anyway; don't be lazy, it doesn't pay off in the
> long run!
> http://www.aspfaq.com/2096|||> OK, I'll give it a try however I have over 50 columns so kind of a
> pain.
CREATE VIEW tmp_vw_foo
AS
SELECT * FROM SomeTable
GO
SELECT COLUMN_NAME+',' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tmp_vw_foo'
ORDER BY ORDINAL_POSITION
The most work you're going to do, aside from copy and paste, is deleting the
last comma.
Yep, that's a real pain. :-)|||Sweet. Thanks for that Aaron.
Aaron Bertrand [SQL Server MVP] wrote:
> CREATE VIEW tmp_vw_foo
> AS
> SELECT * FROM SomeTable
> GO
> SELECT COLUMN_NAME+',' FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'tmp_vw_foo'
> ORDER BY ORDINAL_POSITION
> The most work you're going to do, aside from copy and paste, is deleting t
he
> last comma.
> Yep, that's a real pain. :-)