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