SQL Select Insert Help

Hello EE,

I have this table (see Text file)
Basically, I would like is, based on the Rank column, where 1 is most prioritized,
If within a parameter I passed a UPC Number, lets say we take  803500000569 (its rank is 2)

I would like to insert in another table or I could add a column in the same table and do an update (whatever the easiest)
and write a "Final Rank" to it. That finalRank would be a kind of Row Number so that every line in that table would get a number. no duplicates.  Let me explain

If I send as the first param upc 803500000569, it would start at FinalRank 1.
then I send as param another upc lets say 803500923127. this upc is on Rank 1 and Rank 2. so it go on FinalRank 1 and the previous UPC would take the FinalRank 2

then is I take upc 803500923110 you see in text file this one is Rank 1 on two rows but if you look at the Cust Column, its the same Customer number, it means that upc belongs to the same order so it must go within the rank of that customer to try to complete the order the fastest. so that UPC would take FinalRank 2 and the previous upc on FinalRank 2 would go FinalRank 3

Then after for a last example, if I take UPC 803500000590, well that one is Rank 2, its a new customer number also so I dont have to try to match it, I could put it FinalRank 4 for now ..

So at the end, I could have multiple solutions, all them good, but it jsut need to respect the order of "Rank" and Customer orders

Could you help me to work this out with a While statement or I dont know, a temp table with some insert or update, I dont from there where to go.

Let me know if I wasnt clear enough on my explanations....

thanks !!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Not really clear, in particular which relevance the customer part has (looks like none).
This is my understanding:
1. If we assume we get the minimum rank for a given UPC (not bothering about customer), we will always have a single entry per UPC:
select @UPC, @MinRank = min(Rank) from tbl where UPC = @UPC group by UPC;

Open in new window

2. We do the same for all rows having a FinalRank.
select UPC, MinRank = min(Rank), FinalRank=min(FinalRank) from tbl where FinalRank is not null group by UPC;

Open in new window

(This is only needed for troubleshooting and tests, we don't use this result).
3. We now need to find the insertion point - the lowest FinalRank of all UPCs with the same rank. We are using the results of 2 directly (no need to store):
select @minFinal=min(FinalRank) from (
  select UPC, MinRank = min(Rank), FinalRank=min(FinalRank) from tbl where FinalRank is not null group by UPC) data where MinRank >= @MinRank

Open in new window

4. All rows starting with that FinalRank need to be incremented by one.
update tbl set FinalRank = FinalRank + 1 where FinalRank >= @minFinal;

Open in new window

5. The current UPC rows (!) get that new FinalRank.
update tbl set FinalRank = @minFinal where upc = @UPC;

Open in new window

This does not get you unique FinalRanks - you have multiple rows for one UPC, and all will get the same FinalRank.
PhilippeRenaudAuthor Commented:
Yes I can have multiple rows for same UPC but It will be different customers

so if one customer was on rank 1 and the same upc for another customer is also Rank 1

Well It would not matter where you put it on either the two it would be the same,
but if the second customer was rank 2, well you would immediatly go with the one at Rank 1

I can try your code and I will tell you if it works or not, do I only need to take all those strings you gave me or do you want to do a full code in one Block
PhilippeRenaudAuthor Commented:
oh Wait I just in my Text file the customers are wrong. Im sorry. Let me change it and you might understand better
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PhilippeRenaudAuthor Commented:
here the new text file
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This rows of test2
UPC          Cust   Rank
---          ----   ----
803500923127 032516 2
803500923127 046107 1
803500923127 051454 2

Open in new window

should result in a Rank of 1, and hence get the highest FinalRank up to that moment?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You'll need to do a little more. I used (incomplete) T-SQL syntax, omitting the declaration of vars, which is required. Are you using the Management Studio's Query Window for this?
And I'll have to add the cases whenever there is no row found.

Another note, what should happen if you provide the same UPC twice?
Does this get you something like what you want?

select cust,po,upc,rank,row_number() over (order by rank,cust,po,upc) as [Final_Rank] from table1

Open in new window

45758 	1014768 	803500923158 	1 	1
46107 	1014769 	803500923103 	1 	2
46107 	1014769 	803500923110 	1 	3
46107 	1014769 	803500923127 	1 	4
47090 	1014770 	803500923103 	1 	5
47090 	1014770 	803500923110 	1 	6
49710 	1014778 	803500928719 	1 	7
49710 	1014778 	803500928726 	1 	8
49710 	1014778 	803500928733 	1 	9
32516 	1013892 	803500000552 	2 	10
32516 	1013892 	803500000569 	2 	11
32516 	1013892 	803500923127 	2 	12
51454 	1013892 	803500000590 	2 	13
51454 	1013892 	803500923127 	2 	14

Open in new window

i'm not sure what order to order the rows by to rank them as you want. Could you indicate on your sample data what the desired final rank for each row is?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Not checking for repeated runs on the same UPC, in T-SQL:
declare @UPC varchar(12)
declare @minRank integer
declare @minFinal integer

set @UPC = '803500923127'

select @MinRank = min(Rank) from tbl where UPC = @UPC;

select @minFinal=min(FinalRank) from (
  select UPC, MinRank = min(Rank), FinalRank=min(FinalRank) from tbl where FinalRank is not null group by UPC
) data where MinRank >= @MinRank;

set @minFinal = isnull(@minFinal, 1)

update tbl set FinalRank = FinalRank + 1 where FinalRank >= @minFinal;
update tbl set FinalRank = @minFinal where upc = @UPC;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Simon, this is no one-time run thing. The UPCs are coming in one after another, and their sequence is important but we know nothing about that sequence.
@Qlemo: OK, I can't make out what the business process is from the OP's description. I'd interpreted it as every time there is a new row in the table it may require a re-ranking of all the rows?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I read it "I have a table filled with data, and another source (e.g. table, web form, application) providing a single UPC".
PhilippeRenaudAuthor Commented:
Let me try both of your day code sample today I will reply back soon
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.