Assign incrementing letters to same value

Question

Post reply

Assign incrementing letters to same value

Talvin Singh

Ten Centuries

Points: 1288
More actions
February 4, 2018 at 6:37 am

#343889

hi all,
i have the following table
OrderID Point
Order1 1
Order2   1
Order3 3
Order4 6
Order5 8
Order6 10
Order7 10
i would like the output as
OrderID Point LetterCode
Order1 1 A
Order2 1 A
Order3 3 B
Order4 6 C
Order5 8 D
Order6 10 E
Order7 10 E
as you can see for each order with same point, the same letter gets assigned. Therefore i can group all the letter codes and see what orders belong to a letter code.
if this could be done within a select statement, i can copy the result into another table, or i can create the first table with LetterCode blanked out, and use an update statement. i would prefer the latter, assuming its faster.
many thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

joujousagem2006 1602 SSCommitted Points: 1779 More actions · Answer 1

create table ALPHABET ( id int IDENTITY(1,1), Characters NCHAR(1) PRIMARY KEY )

DECLARE @asciiCode INT= 65 WHILE @asciiCode <= 90 BEGIN

INSERT ALPHABET ( Characters ) SELECT CHAR(@asciiCode)

SELECT @asciiCode = @asciiCode + 1

END USE [master] GO /****** Object: Table [dbo].[order_point] Script Date: 04/02/2018 18:35:33 ******/

CREATE TABLE [dbo].[order_point]( [order1] [varchar](50) NULL, [point] [int] NULL, [numero2] [nvarchar](50) NULL ) ON [PRIMARY]

GO SET ANSI_PADDING OFF GO INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order1', 1) INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order2', 1) INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order3', 3) INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order4', 6) INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order5', 8) INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order6', 10) INSERT [dbo].[order_point] ([order1], [point]) VALUES (N'Order7', 10)

update C set C.numero2=A.Characters from ALPHABET A INNER JOIN order_point C ON C.point=A.id

select * from ALPHABET

select * from order_point

Talvin Singh Ten Centuries Points: 1288 More actions · Answer 2

not quite.

what the above code does is links the point ON the id in alphabet table.

Point can be any number ie see below.

OrderID Point
Order1 4
Order2 12
Order3 12
Order4 12
Order5 29
Order6 31
Order7 49

I have ordered the list by Point. therefore it starts with A and checks if there is another equal Point, if so then assign A, else B, checks if theres another Point equal to B, and so on, like below:

OrderID Point LetterCode
Order1 4 A
Order2 12 B
Order3 12 B
Order4 12 B
Order5 29 C
Order6 31 D
Order7 49 E

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Talvin Singh - Sunday, February 4, 2018 11:12 AM
not quite.
what the above code does is links the point ON the id in alphabet table.
Point can be any number ie see below.
OrderID Point
Order1 4
Order2 12
Order3 12
Order4 12
Order5 29
Order6 31
Order7 49
I have ordered the list by Point. therefore it starts with A and checks if there is another equal Point, if so then assign A, else B, checks if theres another Point equal to B, and so on, like below:
OrderID Point LetterCode
Order1 4 A
Order2 12 B
Order3 12 B
Order4 12 B
Order5 29 C
Order6 31 D
Order7 49 E

What do you want done when there are more than 26 different quantities. Also, two way street here... what is the business reason for wanting to do this (and I mean something more than "It was a requirement that someone gave me", if you don't mind).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Talvin Singh Ten Centuries Points: 1288 More actions · Answer 4

it would for sure stay under 26 letters, i think the max ive counting is around 15 letters.
business reason: my university assignment and i want to group these orders by LetterCode. the lettercodes represent a space on a shelf (this is a spare parts business)

Talvin Singh Ten Centuries Points: 1288 More actions · Answer 5

to be honest, it could be a numberCode rather than letterCode, i just need a way to group the Points together.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Talvin Singh - Sunday, February 4, 2018 12:15 PM
to be honest, it could be a numberCode rather than letterCode, i just need a way to group the Points together.

No problem.

Shifting gears a bit, you'll get faster and better answers in the future if you provide your data in a readily consumable format, like this....
SELECT * INTO #TestTable FROM (VALUES ('Order1', 4) ,('Order2',12) ,('Order3',12) ,('Order4',12) ,('Order5',29) ,('Order6',31) ,('Order7',49) )v(OrderID,Point) ;

Try this with that data. I threw in one of the obvious extra columns that I thought you might also be looking for.
SELECT OrderID ,Point ,GroupCode = DENSE_RANK()OVER (ORDER BY Point) ,OrdersInGroup = COUNT(*) OVER (PARTITION BY Point) FROM #TestTable ORDER BY GroupCode, OrderID ;

Here are the results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Talvin Singh Ten Centuries Points: 1288 More actions · Answer 7

that works very well. Thought it would be a more complicated code!
and correct on the new column, i needed that too!

Expanding the code, looking at the new column [OrdersInGroup], a max of 5 orders can only fit on a shelf space, and there are a max of 6 locations. If the current result is PickGroup 1:

a new wave of orders come through and have similar Points:
SELECT * INTO #TestTable FROM (VALUES ('Order8', 4) ,('Order9',4) ,('Order10', 8) ,('Order11',12) ,('Order12',12) ,('Order13',19) ,('Order14',24) ,('Order15',4) ,('Order16',4) ,('Order17',10) )v(OrderID,Point);

i need to check if there is "free space" on the shelf, order8 would go into groupCode 1 as there now 2 orders therefore can be in PickGroup 1, order10 is a new Point therefore will go into next open shelf location groupCode 6, order11 and 12 drop in and go into groupCode 2, but now reached max 5. therefore that location is now free, the next order13 can now occupy it, therefore it will now wait for other orders for Point 19.
there are no shelf locations (groupCode) open as all are occupied. order14 must therefore be missed, PickGroup 0, orders 15 and 16 go into groupCode 1 and is now full therefore 1 becomes free for next order to be part of PickGroup 2....and so on.

missed orders (pickGroup = 0) will get allocated when no new sets of orders drop in using same method above.

maybe first x orders sets the Points until all 6 GroupCodes have been allocated, then from there iterate through each incoming order?

OrdersInGroup could be a incrementing value, rather than a count?

i'm applying this method on a small dataset of 50 orders, but a typical dataset could hold 10k orders, therefore i would benefit from fast processing...
Many thanks for your help