Calculate the position for a combination of columns

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  December 12, 2012 at 3:15 am

  #286117

  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

  SSChampion

  Points: 10836

  More actions

  December 12, 2012 at 3:51 am

  #1567797

  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[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter

  

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  December 12, 2012 at 7:46 am

  #1567895

  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