Let One Row Represent a Group, Take 2

October 27, 2010 Ted Holt

Let’s return to a problem I wrote about a few months ago. The technique I illustrated in that article works for most implementations of SQL. Today it’s my pleasure to share a technique you can use with versions of SQL that include certain OLAP functions, such as DB2 for i V5R4 or above.

You’ve been asked to list customer orders that have not been fully shipped, but you are to only show the first unshipped line of each order. Use the RANK() function to assign a sequential number to each row for each order, like this:

select OrderNo, LineNo, QtyOrdered - QtyShipped as Balance,
       substr(char(CrtTime), 1, 16) as TimeEntered,
       rank() over
          (partition by OrderNo order by CrtTime) as OrderRank
     from salesordd
    where QtyShipped < QtyOrdered

RANK() OVER means that SQL is to number the rows.
ORDER BY CrtTime means that the rows are to be numbered in sequence according to the CrtTime field.
PARTITION BY OrderNo means that the numbering is to restart at 1 for each order number. Without PARTITION BY, the entire result set would be numbered in one sequence.

This is the result set:

ORDERNO  LINENO   BALANCE   TIMEENTERED       ORDERRANK
    101      2         12   2010-04-21-11.15          1
    102      1          5   2010-04-21-10.05          1
    102      2        100   2010-04-21-10.15          2
    103      3          5   2010-04-21-14.37          1
    105      2          6   2010-04-21-10.30          1

Notice that there are two unshipped lines for order 102. In order to get only one line per order, you must select the rows with a rank value of 1. However, it is not permitted to refer to the ranking column in the WHERE clause. To get around this limitation, put the result set into something temporary, such as a common table expression:

with Temp as 
  (select d.*, 
          rank() over
            (partition by OrderNo order by CrtTime) as OrderRank
     from salesordd as d
    where QtyShipped < QtyOrdered)
    
select t.OrderNo, t.LineNo, t.QtyOrdered, t.QtyShipped,
       (t.QtyOrdered - t.QtyShipped) as Balance, t.Price,
       (t.QtyOrdered - t.QtyShipped) * t.Price as Extended,
       substr(char(t.crttime), 1, 16) as TimeEntered 
  from temp as t
 where t.OrderRank = 1

Or a derived table:

select t.OrderNo, t.LineNo, t.QtyOrdered, t.QtyShipped,
       (t.QtyOrdered - t.QtyShipped) as Balance, t.Price,
       (t.QtyOrdered - t.QtyShipped) * t.Price as Extended,
       substr(char(t.crttime), 1, 16) as TimeEntered
  from 
  (select d.*,
          rank() over
            (partition by OrderNo order by CrtTime) as OrderRank
     from salesordd as d
    where QtyShipped < QtyOrdered) as t
 where t.OrderRank = 1

Either way, here’s the result set:

ORDER  LINE  ORDERED  SHIPPED   BALANCE   PRICE  EXTENDED  TIMEENTERED
  101    2        12        0        12    1.00     12.00  2010-04-21-11.15
  102    1        10        5         5    2.00     10.00  2010-04-21-10.05
  103    3         5        0         5    1.10      5.50  2010-04-21-14.37
  105    2         6        0         6    2.00     12.00  2010-04-21-10.30