Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate the position for a combination of columns


Calculate the position for a combination of columns

Author
Message
Paul Hernández
Paul Hernández
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 654
Hi guys,

I have the following situation:

A table with receipts key and item key. One receipt has one or more items. I would like to query this table and for every receipt assing a position number to every item.

Example:

Receipt_key Item_key Position
0001 02654 1
0001 35544 2
0002 02654 1
0002 85466 2
0002 84945 3


I`ve spent some time but I can´t figure out a solution.

Any comment would be appreciated.

Kind Regards

Paul Hernández
Lokesh Vij
Lokesh Vij
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1564 Visits: 1599
You can do it like this:

Creating temp table and inserting test values
create table #temp
(Receipt_key varchar(10),
Item_key varchar(10) ,
Position int);

insert #temp
values
('0001','02654',NULL),
('0001','35544',NULL),
('0002','02654',NULL),
('0002','85466',NULL),
('0002','84945',NULL)


Final update statement
WITH upd_recs
AS (SELECT Receipt_key,
Item_key,
Row_number()
OVER (
partition BY Receipt_key
ORDER BY Receipt_key, Item_Key) rnk
FROM #temp)
UPDATE #temp
SET Position = rnk
FROM upd_recs a
INNER JOIN #temp b
ON a.Receipt_key = b.Receipt_key
AND a.Item_key = b.Item_key



Hope this is what you are looking for?

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Paul Hernández
Paul Hernández
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 654
Hi Lokesh Vij,

thanks so much for your quick and HELPFUL answer. That´s right, that is exactly what I needed.

In fact, I´m loading the final table using an SSIS DFT, I used a simplify version of my query in the original question, with this part of your query was enough:



SELECT Receipt_key,
Item_key,
Row_number()
OVER (
partition BY Receipt_key
ORDER BY Receipt_key, Item_Key) rnk
FROM [My Transaction Table]




The key point is the OVER statement, very useful, very handy.

Thanks again for helping me to learn something new .

Best Regards,

Paul

Paul Hernández
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search