extra column with numbering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

extra column with numbering

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-05-14 : 05:39:22

select branchname from salesbranches order by branchname

For the above query, I get the values like
A
B
C
D

Can I also get another column by making changes to the query which gives auto numbering like

A 1
B 2
C 3
D 4

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 07:01:48

Assuming you want to order alphabetically, you could do this if you just want the results in a query:

SELECT
   branchname,
   ROW_NUMBER() OVER (ORDER BY branchnam) as BrachNumber
FROM
   salesbranches;

You can of course, add another column and update it with these values if you want to store them in the table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-14 : 07:03:12

Alternative you can do this in front end application if you want to show data there

Madhivanan

Failing to plan is Planning to fail

gagani
Posting Yak Master

112 Posts

Posted - 2012-05-14 : 10:44:17

row_number() is not available in sql 2000 i think. could you
please write the equivalent to be useful for sql 2000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 12:45:23

quote:
Originally posted by gagani

row_number() is not available in sql 2000 i think. could you
please write the equivalent to be useful for sql 2000

I am not very familiar with SQL 2000. May be you can do this in your client application as Madhivanan suggested. Here is a possible thought, but I haven't tested it because I don't have SQL 2000, and even if it runs, it is likely to be painfully slow.

SELECT
   branchname,
   (SELECT COUNT(*) FROM salesbranches b WHERE b.branchname <= a.branchname)
FROM
   salesbranches a;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 22:22:44

in SQL 2000 only way you can guarantee the equivalent functionality for row_number is by using an intermediate temporary table


CREATE TABLE #Temp
(
ID int IDENITY(1,1) NOT NULL,
branchname varchar(10)
)

INSERT #Temp
SELECT branchname 
FROM salesbranches
ORDER BY branchname

SELECT *
FROM #Temp

Though Sunitas method will work so long as branchnames are unique, if you happen to have duplicates it will not generate row numbers accurately

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-16 : 04:25:02

Alternatively use quirky update
http://beyondrelational.com/modules/2/blogs/70/posts/10865/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources