Some clever SQL needed - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Some clever SQL needed

Author

Topic

kevindockerty
Starting Member

27 Posts

Posted - 2010-08-10 : 14:27:59

Hi
I have a table that contains data as below.

part_no, sp_start, sp_order
1001 01/01/2001 0
1001 01/09/2002 0
1001 01/01/2003 0
1002 01/01/2001 0
1003 01/02/2003 0
1003 01/03/2004 0
1004 01/01/2001 0
1004 01/01/2002 0
1004 01/01/2003 0
1004 01/01/2003 0

I need to update the sp_order field to show the chronological order of each row for each part such I should end up with;

part_no, sp_start, sp_order
1001 01/01/2001 1
1001 01/09/2002 2
1001 01/01/2003 3
1002 01/01/2001 1
1003 01/02/2003 1
1003 01/03/2004 2
1004 01/01/2001 1
1004 01/01/2002 2
1004 01/01/2003 3
1004 01/01/2003 4

I can do it with a cursor but I'm trying to avoid that.
Note - The number for sp_order may go up to 10

I can do the update for all the sp_order 1's easy enough.
any ideas much appreciated
Kev D

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-10 : 14:38:28

[code]update a
set a.sp_order = b.seq
from YourTable a
inner join (select row_number() over(partition by part_no order by sp_start) as seq, * from YourTable ) b
on a.part_no = b.part_no and a.sp_start = b.sp_start[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-10 : 14:46:32

no need of join
just the below will suffice


UPDATE t
SET t.sp_order= t.Seq
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY part_no ORDER BY sp_start) AS Seq,sp_order
FROM Table
)t

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-10 : 14:51:17

DECLARE @Table Table (Part_no smallint,sp_start smalldatetime,sp_order tinyint)

INSERT INTO @TABLE
SELECT 1001,'01/01/2001', 0 UNION ALL
SELECT 1001,'01/09/2002', 0 UNION ALL
SELECT 1001,'01/01/2003', 0 UNION ALL
SELECT 1002,'01/01/2001', 0 UNION ALL
SELECT 1003,'01/02/2003', 0 UNION ALL
SELECT 1003,'01/03/2004', 0 UNION ALL
SELECT 1004,'01/01/2001', 0 UNION ALL
SELECT 1004,'01/01/2002', 0 UNION ALL
SELECT 1004,'01/01/2003', 0 UNION ALL
SELECT 1004,'01/01/2003', 0

DECLARE @order tinyint
DECLARE @part_no smallint
SET @order = 0

UPDATE @Table
SET

@order = [sp_order] = CASE WHEN @part_no = part_no THEN @order + 1 ELSE 1 END
, @part_no = part_no

select * from @table

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-10 : 14:58:42

quote:
Originally posted by jimf

DECLARE @Table Table (Part_no smallint,sp_start smalldatetime,sp_order tinyint)

INSERT INTO @TABLE
SELECT 1001,'01/01/2001', 0 UNION ALL
SELECT 1001,'01/09/2002', 0 UNION ALL
SELECT 1001,'01/01/2003', 0 UNION ALL
SELECT 1002,'01/01/2001', 0 UNION ALL
SELECT 1003,'01/02/2003', 0 UNION ALL
SELECT 1003,'01/03/2004', 0 UNION ALL
SELECT 1004,'01/01/2001', 0 UNION ALL
SELECT 1004,'01/01/2002', 0 UNION ALL
SELECT 1004,'01/01/2003', 0 UNION ALL
SELECT 1004,'01/01/2003', 0

DECLARE @order tinyint
DECLARE @part_no smallint
SET @order = 0

UPDATE @Table
SET

@order = [sp_order] = CASE WHEN @part_no = part_no THEN @order + 1 ELSE 1 END
, @part_no = part_no

select * from @table

Jim

Everyday I learn something that somebody else already knew

for this to work properly it depends on availability of clustered index on part_no

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-10 : 15:01:38

see
http://visakhm.blogspot.com/2010/03/using-quirky-updates-to-develop-well.html

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

kevindockerty
Starting Member

27 Posts

Posted - 2010-08-11 : 04:55:38

its 2005 not 2000 - sorry my mistake - hence I havent got rownumber, over partition etc..

these methods wont work either - I am dealing with thousands of rows in reality
...INSERT INTO @TABLE
...SELECT 1001,'01/01/2001', 0 UNION ALL
...SELECT 1001,'01/09/2002', 0 UNION ALL

apologies
Kev D

kevindockerty
Starting Member

27 Posts

Posted - 2010-08-11 : 04:56:08

sorry make that.....
ITS 2000 NOT 2005 !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-11 : 05:02:24

If you want to show data in front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

kevindockerty
Starting Member

27 Posts

Posted - 2010-08-11 : 05:33:17

Madhivanan - I dont want to show data in a front end application

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-11 : 05:42:52

Try the method posted by jimf

Madhivanan

Failing to plan is Planning to fail

kevindockerty
Starting Member

27 Posts

Posted - 2010-08-11 : 07:05:40

Madhivan
Jimf method wont work
I have about 50,000 records not just the 10 or so in my example

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-11 : 07:15:23

Why won't it work on 50,000?

Jim

Everyday I learn something that somebody else already knew

kevindockerty
Starting Member

27 Posts

Posted - 2010-08-11 : 09:06:06

sorry, Jimf's solution does work - I had'nt read it correctly

nice one Jim and thanks - very neat

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-11 : 10:40:50

Thanks, but it's not my solution - hence my sig line! And as Visakh pointed out make sure you have the clustered index

http://visakhm.blogspot.com/2010/03/using-quirky-updates-to-develop-well.html

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources