Renumbering using CTE - 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 2008 Forums

Transact-SQL (2008)

Renumbering using CTE

Author

Topic

jkbeau1
Starting Member

12 Posts

Posted - 2015-04-20 : 08:50:29

The following contains the temporary table on the top that I want to work on and the bottom is the result I am looking for:

Basically I'd like to use CTE to get the sort order numbering to be sequential, as you can see in the second table above. The CTGY_Rec_Num is always unique and a group of CTGY_Rec_Num numbers belongs to a specific DATB_Rec_Num. As you will note, the CTGY_Sort_Order is sorted but there are gaps and it can start back at 1 between DATB_Rec_Num groupings.

I've messed with a bunch of different CTE attempts with no luck. I'm kind of new to CTE and don't want to have to resort to a loop or a cursor as I am sure this can be done with the recursive nature of CTE.

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-20 : 09:06:46

Can't view your image. Please post your CTE query.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-20 : 09:23:56

You should post test data in a consumable format.

I suspect you want something like:


SELECT *
	,ROW_NUMBER() OVER (PARTITION BY DATB_Rec_Num ORDER BY CTGY_Sort_Order) AS NEW_Sort_Num
FROM YourTable;

If you want to update CTGY_Sort_Order:


WITH NewOrder
AS
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY DATB_Rec_Num ORDER BY CTGY_Sort_Order) AS NEW_Sort_Num
	FROM YourTable
)
UPDATE NewOrder
SET CTGY_Sort_Order = NEW_Sort_Num
WHERE CTGY_Sort_Order <> NEW_Sort_Num;

-- see results
select * from YourTable;

jkbeau1
Starting Member

12 Posts

Posted - 2015-04-20 : 10:23:46

Fairly new to the forum so when you say "consumable format", what would be the best way to do that within this forum?

The CTE provided doesn't quite work as expected as for simplicity sake I left out the fact that within each DATB_Rec_Num, CTGY_Rec_Num grouping, there are items...

So for DATB_Rec_Num = 1, CTGY_Rec_Num = 1, there could be items 10000, 10001, 10002, 10003, 10004, 10005, etc. The CTE provided would generate a sort number for each item whereas if the new sort number is 1, it would be 1 for each item until the CTGY_Rec_Num changes.

Probably shouldn't have left this detail out...sorry!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-20 : 10:30:36

Follow this guide:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

jkbeau1
Starting Member

12 Posts

Posted - 2015-04-20 : 11:03:24

OK, try this to create the test data...

create table #Table
(
CTGY_Rec_Num int,
CTGY_Sort_Order int,
DATB_Rec_Num int,
Original_Item_Number int,
NEW_Sort_Order int
);

insert into #Table
(CTGY_Rec_Num, CTGY_Sort_Order, DATB_Rec_Num, Original_Item_Number)
select 1, 1, 1, 10000 union all
select 1, 1, 1, 10001 union all
select 2, 2, 1, 10002 union all
select 2, 2, 1, 10003 union all
select 3, 4, 1, 10004 union all
select 3, 4, 1, 10005 union all
select 4, 7, 1, 10006 union all
select 4, 7, 1, 10007 union all
select 5, 8, 1, 10008 union all
select 5, 8, 1, 10009 union all
select 6, 10, 1, 10010 union all
select 6, 10, 1, 10011 union all
select 7, 15, 1, 10012 union all
select 10, 1, 2, 20000 union all
select 10, 1, 2, 20001 union all
select 12, 2, 2, 20002 union all
select 12, 2, 2, 20003 union all
select 13, 3, 2, 20004 union all
select 13, 3, 2, 20005 union all
select 14, 4, 2, 20006 union all
select 14, 4, 2, 20007 union all
select 15, 5, 2, 20008 union all
select 15, 5, 2, 20009 union all
select 16, 7, 2, 20010 union all
select 16, 7, 2, 20011 union all
select 17, 10, 2, 20012;

So the result of the correct CTE would populate the NEW_Sort_Order column like:

1, 1, 1, 10000, 1
1, 1, 1, 10001, 1
2, 2, 1, 10002, 2
2, 2, 1, 10003, 2
3, 4, 1, 10004, 3
3, 4, 1, 10005, 3
4, 7, 1, 10006, 4
4, 7, 1, 10007, 4
5, 8, 1, 10008, 5
5, 8, 1, 10009, 5
6, 10, 1, 10010, 6
6, 10, 1, 10011, 6
7, 15, 1, 10012, 7
10, 1, 2, 20000, 1
10, 1, 2, 20001, 1
12, 2, 2, 20002, 2
12, 2, 2, 20003, 2
13, 3, 2, 20004, 3
13, 3, 2, 20005, 3
14, 4, 2, 20006, 4
14, 4, 2, 20007, 4
15, 5, 2, 20008, 5
15, 5, 2, 20009, 5
16, 7, 2, 20010, 6
16, 7, 2, 20011, 6
17, 10, 2, 20012, 7

Hopefully that makes my question and expected results a little clearer?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-20 : 11:16:13

[code]
,DENSE_RANK() OVER (PARTITION BY DATB_Rec_Num ORDER BY CTGY_Sort_Order) AS NEW_Sort_Num
[/code]

jkbeau1
Starting Member

12 Posts

Posted - 2015-04-20 : 14:10:01

That will work...thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources