SQL - sequencing column data in the table

Question

I have a table with 3 fields:

CUST_ORDER  FULFILL_NO  ITEM  LOCATION
   SA23        1        0233    11001
   SA23        1        0243    13001
   SA23        1        0513    14001
   SA88        1        0873    15001
   SA88        1        0533    17001

I want to sequence the fulfill_no field so that data becomes:

CUST_ORDER  FULFILL_NO  ITEM  LOCATION
   SA23        1        0233    11001
   SA23        2        0243    13001
   SA23        3        0513    14001
   SA88        1        0873    15001
   SA88        2        0533    17001

How to do that ?

is there an order you have to follow? it is inconsistent in the sample data. — Vamsi Prabhala, Jan 12 '17 at 16:13

Gordon Linoff · Accepted Answer · 2017-01-12T16:14:29.420

You can use row_number():

select cust_order,
       row_number() over (partition by cust_order order by location) as fulfill_no,
       item, location
from t;

Actually updating the data can be tricky in Oracle. Here is one way:

update t
    set fulfill_no = (select count(*)
                      from t t2
                      where t2.cust_order = t.cust_order and t2.location <= t.location
                     );

score 0 · Answer 2 · edited May 23 '17 at 11:52

I'd like to add to Gordon Linoff's answer. I'd like to point out that Gordon's row_number() solution works in Microsoft SQL Server, Oracle, and PostgreSQL. This answer probably works for the questioner, given the oracle tag on the question.

If a MySQL user finds this question and needs a solution, they should use a session variable instead. Implementing row_number() in MySQL was asked on stackoverflow already: ROW_NUMBER() in MySQL I'm sorry I don't have MySQL available to test with, but to try my hand at customizing the above query to MySQL...

SELECT
   t.CUST_ORDER,
   (@rownum := @rownum + 1) AS FULFILL_NO,
   t.ITEM,
   t.LOCATION
FROM YOUR_TABLE t,
   (SELECT @rownum := 0) r
ORDER BY t.CUST_ORDER, t.LOCATION

For more advanced usage, you could try this link. http://www.mysqltutorial.org/mysql-row_number/

SQL - sequencing column data in the table

2 Answers2