Add auto number field to view

9 Jun 2006 4:54 PM

JR

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

9 Jun 2006 4:59 PM

Aaron Bertrand [SQL Server MVP]

http://www.aspfaq.com/2427

Show quote

"JR" <jrik***@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
>

9 Jun 2006 5:11 PM

JR

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:
Show quote

> http://www.aspfaq.com/2427
>
>
>
>
> "JR" <jrik***@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
> >

9 Jun 2006 5:16 PM

Aaron Bertrand [SQL Server MVP]

>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

9 Jun 2006 5:34 PM

JR

OK, I'll give it a try however I have over 50 columns so kind of a
pain.

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

> >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

9 Jun 2006 5:45 PM

Aaron Bertrand [SQL Server MVP]

> 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. :-)

9 Jun 2006 9:56 PM

JR

Sweet. Thanks for that Aaron.

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

> > 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. :-)

9 Jun 2006 5:04 PM

Alexander Kuznetsov

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()

9 Jun 2006 5:09 PM

JR

Using SQL Server 2000.

Alexander Kuznetsov wrote:
Show quote

> 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()