Classic SQL Technique to generate row numbers

Idea of this post, is to preserve the classic old technique of generating Row Numbers using SQL.
Simple answer for this can be to use ROW_NUMBER()..OVER..PARTITION BY clause in Oracle.

People used to generate row numbers, even before this clause was instroduced.
Here is an example, which explains this technique.

Consider ORDERS table which has the data as shown below.
One Order can contain multiple products. (Sample Script here)

Now, assume the sequence number has to be generated for each order as shown below.

Using ROW_NUMBER() Function:
SELECT o.*, ROW_NUMBER ()
       OVER (PARTITION BY order_id ORDER BY product_id) AS SeqNo
   
  FROM orders o;

Without using ROW_NUMBER() Function:
SELECT   t4.seqno, t1.*
    FROM orders t1
         JOIN
         (SELECT   COUNT (*) seqno, t2.product_id, t2.order_id
              FROM orders t2 INNER JOIN orders t3
                   ON t2.product_id >= t3.product_id
                 AND t2.order_id = t3.order_id
          GROUP BY t2.product_id, t2.order_id) t4
         ON t4.order_id = t1.order_id AND t4.product_id = t1.product_id
ORDER BY t1.order_id, t1.product_id;


Pros:

• Suitable if you are working with small databases like SQLLite. Note that, this is SQL technique which involves only joins and can be used with any database.

Cons:

• Slow, if we are talking tables with large amount of data.
• Won't work if the sequence number has to be generated w.r.to VARCHAR column. It only work with NUMBER column.

Like this topic? Jump on to comments section and leave your feedback.

Cheers!!