Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate the position for a combination of columns Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, December 12, 2012 3:15 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, December 06, 2013 2:36 AM Points: 94, Visits: 365
 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ándezhttp://hernandezpaul.wordpress.com/https://twitter.com/paul_eng
Post #1395544
 Posted Wednesday, December 12, 2012 3:51 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 02, 2013 4:38 AM Points: 1,322, Visits: 1,501
 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
Post #1395562
 Posted Wednesday, December 12, 2012 7:46 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, December 06, 2013 2:36 AM Points: 94, Visits: 365
 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ándezhttp://hernandezpaul.wordpress.com/https://twitter.com/paul_eng
Post #1395678

 Permissions