NEED ANSWERS FOR THESE QUESTIONS

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

NEED ANSWERS FOR THESE QUESTIONS

Author

Topic

Swami99
Starting Member

1 Post

Posted - 2013-06-01 : 06:23:01

hi friends , what SQL statements and queries i use to modify column.
i created table which contains emp_id column of numers. As i deleted some data now numbers are not in asecnending order. could u please tell me which query i use to arrange the same.
numbers are shows as 1,6,7,2,3
and neeed to arrange as 1,2,3,....

Dhananjay

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-01 : 08:18:52

Just add order by clause to your select statement.

[CODE]
SELECT emp_id from TABLE ORDER BY emp_id
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 20:19:38

you can generate it on the fly if you want display as 1,2,3,...


SELECT emp_id,ROW_NMBER() OVER (ORDER BY emp_id) AS Seq from TABLE ORDER BY Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-06-02 : 12:59:10

quote:
Originally posted by visakh16

you can generate it on the fly if you want display as 1,2,3,...

SELECT emp_id,ROW_NMBER() OVER (ORDER BY emp_id) AS Seq from TABLE ORDER BY Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I think OP wants to generate emp_id in ascending order (which may have gaps because of deletion) and row_number solution may not work as expected

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-06-02 : 22:20:54

using visakh solution?


declare @a table(col1 int, col2 varchar(10))
insert into @a select
1,'alie' union all select
6,'abu' union all select
7,'ah chan';

update	a
set		col1 = rn
from	(select
			ROW_NUMBER() over (order by col1)rn
		,	*
		from	@a)a

select * from @a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 02:16:16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

you can generate it on the fly if you want display as 1,2,3,...

SELECT emp_id,ROW_NMBER() OVER (ORDER BY emp_id) AS Seq from TABLE ORDER BY Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I think OP wants to generate emp_id in ascending order (which may have gaps because of deletion) and row_number solution may not work as expected

Madhivanan

Failing to plan is Planning to fail

Why?
rownumber will still give values in correct sequence 1,2,3 etc
OP can either display it in select or if wanted can do the change in table also as Waterduck showed.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-06-03 : 05:11:17

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

you can generate it on the fly if you want display as 1,2,3,...

SELECT emp_id,ROW_NMBER() OVER (ORDER BY emp_id) AS Seq from TABLE ORDER BY Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I think OP wants to generate emp_id in ascending order (which may have gaps because of deletion) and row_number solution may not work as expected

Madhivanan

Failing to plan is Planning to fail

Why?
rownumber will still give values in correct sequence 1,2,3 etc
OP can either display it in select or if wanted can do the change in table also as Waterduck showed.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Ok. You have also specified emp_id in SELECT statement. I did not notice it

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 05:16:15

quote:
Originally posted by madhivanan
Ok. You have also specified emp_id in SELECT statement. I did not notice it

Madhivanan

Failing to plan is Planning to fail

Oh I see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources