## Calculate the position for a combination of columns

 Author Message Paul Hernández SSCrazy Group: General Forum Members Points: 2730 Visits: 671 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 Position0001 02654 10001 35544 20002 02654 10002 85466 20002 84945 3I`ve spent some time but I can´t figure out a solution.Any comment would be appreciated.Kind Regards Paul Hernández Lokesh Vij SSCertifiable Group: General Forum Members Points: 5904 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 #tempvalues('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 #tempSET Position = rnkFROM 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 questionGuidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.comFollow me @Twitter Paul Hernández SSCrazy Group: General Forum Members Points: 2730 Visits: 671 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