Help required in query.

Question

Help required in query.

amitsingh308

Ten Centuries

Points: 1287
More actions
April 6, 2016 at 9:49 am

#306254

Hello Everyone,
I have a transactional table which contains following columns
OrderNo. ItemNo. ItemName
But there is an issue I need to rearrange the item number if order number changed and assign ItemNo 1 to 20.
Please help me.

Viewing 13 posts - 1 through 12 (of 12 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Please provide a create table statement, insert statements to set up the data, then expected output for what you are asking. We can't write a query without that.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Phil Parkin SSC Guru Points: 246963 More actions · Answer 2

amitsingh308 (4/6/2016)
Hello Everyone,
I have a transactional table which contains following columns
OrderNo. ItemNo. ItemName
But there is an issue I need to rearrange the item number if order number changed and assign ItemNo 1 to 20.
Please help me.

Also, please state the nature of the help you need and what you have tried so far. We're here to assist, not to do your job for you.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

amitsingh308 (4/6/2016)
Please help me.

Help us help you. Please see the first link under "Helpful Links" in may signature line below.

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

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 4

Create table Script is as under:

Create table OrdersDetails(OrderNo varchar(30),ItemNo Varchar(20),ItemName Varchar(50))

insert script is as under:

Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12231','1','sdfasfasd')

Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12231','2','sdfasfasdas2')

Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12231','1','sdfasfasd')

Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12232','1','sdfasfasdas2')

Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12232','1','sdfasfasd11')

Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12233','1','sdfasfasdasd2')

Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('122314','1','sdfasfasd123')

Now I want to change sequence number of ItemNo for order number 12231 1 to 3.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What sequence number? There's no 'sequence' column in any of those tables, no indication where it comes from, how it's calculated.

We can't read your mind, we can't see your screen. Please explain what you're trying to do in enough detail that someone can actually help you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MadAdmin SSChampion Points: 11261 More actions · Answer 6

amitsingh308 (4/7/2016)
Create table Script is as under:
Create table OrdersDetails(OrderNo varchar(30),ItemNo Varchar(20),ItemName Varchar(50))
insert script is as under:
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('122314','1','sdfasfasd123')
Now I want to change sequence number for order number 12231 1 to 3.

Probably off topic, but shouldn't there be an item table which contains the itemno and the itemname?

Just so that you dont repeat massive description sstrings in a highly transactional table, i.e. you save the value of that attribute once.

Or is this a Datawarehouse table where you want to have denormalised data?

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Phil Parkin SSC Guru Points: 246963 More actions · Answer 7

MadAdmin (4/7/2016)
amitsingh308 (4/7/2016)
Create table Script is as under:
Create table OrdersDetails(OrderNo varchar(30),ItemNo Varchar(20),ItemName Varchar(50))
insert script is as under:
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('122314','1','sdfasfasd123')
Now I want to change sequence number for order number 12231 1 to 3.
Probably off topic, but shouldn't there be an item table which contains the itemno and the itemname?
Just so that you dont repeat massive description sstrings in a highly transactional table, i.e. you save the value of that attribute once.
Or is this a Datawarehouse table where you want to have denormalised data?

In the very first post, it was described as a transactional table, so you are bang on with your question. ItemName does not belong here.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

amitsingh308 (4/7/2016)
Create table Script is as under:
Create table OrdersDetails(OrderNo varchar(30),ItemNo Varchar(20),ItemName Varchar(50))
insert script is as under:
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12231','1','sdfasfasd')
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12231','2','sdfasfasdas2')
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12231','1','sdfasfasd')
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12232','1','sdfasfasdas2')
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12232','1','sdfasfasd11')
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12233','1','sdfasfasdasd2')
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('122314','1','sdfasfasd123')
Now I want to change sequence number of ItemNo for order number 12231 1 to 3.

We might have a bit of a language disparity going on here. Like Gail says, we're not seeing a "sequence" number here. Since ItemNo 12231 has 3 items in it, do you really mean that you just want a "count for each ItemNo"?

For the data you've given above, it would be really helpful if you posted the expected output because with what you've stated so far, we have no idea what your original problem below means.

I have a transactional table which contains following columns
OrderNo. ItemNo. ItemName
But there is an issue I need to rearrange the item number if order number changed and assign ItemNo 1 to 20.

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

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 9

Yes I want to count each item in each order and assign serial number to each item.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

amitsingh308 (4/10/2016)
Yes I want to count each item in each order and assign serial number to each item.

no idea really, because you haven't given us your expected results based on your sample data.

try this for starters

SELECT OrderNo,

ItemNo,

ItemName,

ROW_NUMBER() OVER(PARTITION BY OrderNo ORDER BY Itemno) rn

FROM OrdersDetails;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MadAdmin SSChampion Points: 11261 More actions · Answer 11

amitsingh308 (4/7/2016)
Create table Script is as under:
Create table OrdersDetails(OrderNo varchar(30),ItemNo Varchar(20),ItemName Varchar(50))
insert script is as under:
Insert into OrdersDetails (OrderNo,ItemNo,ItemName) Values('12231','1','sdfasfasd')
Now I want to change sequence number of ItemNo for order number 12231 1 to 3.

Why no create a surrogate Key for the table

i.e.

Create table OrdersDetails(OrdersDetailsID bigint identity(1,1),OrderNo varchar(30),

ItemNo Varchar(20),ItemName Varchar(50),

CONSTRAINT PK_OrderDetails Primary Key Clustered (OrdersDetailsID) )

Then you have a unique value per row that you can use to reference each row.

Also, in my reality (that seems to only work in my head), in the spirit of normalization,

it should really be like this.

CREATE TABLE ItemDetails(ItemID INT IDENTITY(1,1),ItemNo Varchar(20),

ItemName Varchar(50), CONSTRAINT PK_ItemDetails Primary Key Clustered (ItemID) )

CREATE UNIQUE NONCLUSTERED INDEX [AK_ItemNo] ON ItemDetails(ItemNo)ON [PRIMARY]

GO

CREATE TABLE dbo.OrdersDetails(OrdersDetailsID BIGINT identity(1,1),OrderNo varchar(30),

ItemID INT, CONSTRAINT PK_OrderDetails Primary Key Clustered (OrdersDetailsID) )

GO

CREATE UNIQUE NONCLUSTERED INDEX [AK_OrderNoItemID] ON OrdersDetails(OrderNo,ItemID)ON [PRIMARY]

GO

ALTER TABLE dbo.OrdersDetails WITH CHECK ADD CONSTRAINT [FK_OrdersDetails_ItemDetails_ItemID]

FOREIGN KEY(ItemID) REFERENCES dbo.ItemDetails (ItemID)

GO

ALTER TABLE OrdersDetails CHECK CONSTRAINT [FK_OrdersDetails_ItemDetails_ItemID]

GO

This way you can put some rules on the item data, like a unique itemno, as an example.

If this suggestion will not work, then maybe you can give us a table with the new column and before and after state with expected results so that I can see how it will be different in functionality to just having an identity on the table.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

mhallin@maxims-travel.com Newbie Points: 9 More actions · Answer 12

mhallin@maxims-travel.com

Newbie

Points: 9

April 12, 2016 at 1:42 am

#1871313

Didnt read that there as a page 2 🙂