asked on

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 !!!
test.txt

Qlemo

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.

Philippe Renaud

ASKER

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

Philippe Renaud

ASKER

oh Wait I just in my Text file the customers are wrong. Im sorry. Let me change it and you might understand better

Philippe Renaud

ASKER

here the new text file
test2.txt

Qlemo

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?

Qlemo

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?

Simon

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

Results;

CUST 	PO 	UPC 	RANK 	FINAL_RANK
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?

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

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.

Simon

@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?

Qlemo

I read it "I have a table filled with data, and another source (e.g. table, web form, application) providing a single UPC".

Philippe Renaud

ASKER

Let me try both of your day code sample today I will reply back soon