DB2 Top-N and latest-row patterns

Start by translating the requirement

Before writing SQL, ask whether “top three” means three rows, three finishing places, or three distinct values. Those answers differ when two employees have the same salary. Also ask whether “latest” means the greatest business event timestamp, the greatest load timestamp, the greatest sequence number, or the row most recently changed in Db2. A timestamp column does not automatically have the meaning your business wants.

Top N overall with FETCH FIRST

Db2 applies the ordering to the result and then limits the result with the fetch clause. The following query asks for no more than five employees, ranked by salary from greatest to least. EMPNO is a final tie-breaker, so equal salaries still have a complete order.

1
2
3
4
5
SELECT EMPNO, LASTNAME, WORKDEPT, SALARY
FROM DSN8D10.EMP
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC, EMPNO ASC
FETCH FIRST 5 ROWS ONLY;

The predicate excludes unknown salaries. That matters because Db2 treats the null value as higher than non-null values for ordering; with descending order, nulls can appear before real salaries unless you exclude them or state an appropriate null ordering. Always decide explicitly whether unknown values belong in the competition.

FETCH FIRST limits rows; it does not define “best”

Without ORDER BY, the result table has no guaranteed sequence, so “first five” means any five qualifying rows. An index access path that happens to produce a convenient order today is not a contract. RUNSTATS, REBIND, a new index, parallelism, or a Db2 maintenance level can change the access path and expose the mistake.

Even ORDER BY SALARY DESC is incomplete when salaries tie. IBM documents that rows with duplicate values of the final sort key have an arbitrary relative order. Add a stable, unique key such as EMPNO when exactly five repeatable rows are required. Do not use a changing or non-unique attribute as the final key.

Bottom N is the same pattern in the other direction

1
2
3
4
5
SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE
FROM PRODUCT
WHERE UNIT_PRICE IS NOT NULL
ORDER BY UNIT_PRICE ASC, PRODUCT_ID ASC
FETCH FIRST 10 ROWS ONLY;

“Bottom” usually means ascending order, but it might mean worst score, earliest date, or largest defect count. Encode the business meaning rather than assuming ASC always means worst. FETCH FIRST returns at most N rows; it can return fewer when fewer rows qualify.

Ties: ROW_NUMBER, RANK, and DENSE_RANK

These OLAP specifications calculate a value without collapsing detail rows. Their ORDER BY belongs inside the OVER clause and determines ranking. The outer ORDER BY is separate: it controls how the final result is displayed. Use both when consumers need a guaranteed presentation order.

1
2
3
4
5
6
7
8
SELECT EMPNO,
       SALARY,
       ROW_NUMBER() OVER (ORDER BY SALARY DESC, EMPNO) AS ROW_NO,
       RANK()       OVER (ORDER BY SALARY DESC)        AS PAY_RANK,
       DENSE_RANK() OVER (ORDER BY SALARY DESC)        AS PAY_LEVEL
FROM DSN8D10.EMP
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC, EMPNO;

• ROW_NUMBER produces 1, 2, 3, 4 even when salary ties. EMPNO makes the numbering deterministic.
• RANK produces 1, 1, 3 for values 100, 100, 90. It models competition places and leaves a gap after peers.
• DENSE_RANK produces 1, 1, 2 for the same values. It ranks distinct salary levels without gaps.

Notice that PAY_RANK and PAY_LEVEL omit EMPNO from their window ordering. That is intentional: employees with equal salaries must remain peers. Adding EMPNO there would break the tie and give each employee a different rank. The correct ORDER BY list depends on whether you are defining a row winner or a business-value tie.

Filtering a calculated rank requires another query level

A rank calculated in the SELECT list is not available to the WHERE clause of the same query block. Calculate it in a common table expression (CTE) or nested table expression, then filter it outside.

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH RANKED_PAY AS
(
  SELECT EMPNO,
         LASTNAME,
         SALARY,
         RANK() OVER (ORDER BY SALARY DESC) AS PAY_RANK
  FROM DSN8D10.EMP
  WHERE SALARY IS NOT NULL
)
SELECT EMPNO, LASTNAME, SALARY, PAY_RANK
FROM RANKED_PAY
WHERE PAY_RANK <= 3
ORDER BY PAY_RANK, EMPNO;

This returns everyone in the first three competition places, so it can return more than three rows. Replace RANK with DENSE_RANK when the requirement is the top three distinct salary values. Replace it with ROW_NUMBER ordered by salary and EMPNO when the requirement is exactly three employees.

Latest row per group

FETCH FIRST 1 ROW ONLY on the whole query returns one row for the entire result, not one row for every customer. To restart numbering for each customer, use PARTITION BY. The following pattern returns exactly one order per customer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH LATEST_ORDER AS
(
  SELECT CUSTOMER_ID,
         ORDER_ID,
         ORDER_TS,
         STATUS,
         TOTAL_AMOUNT,
         ROW_NUMBER() OVER
         (
           PARTITION BY CUSTOMER_ID
           ORDER BY ORDER_TS DESC, ORDER_ID DESC
         ) AS RN
  FROM CUSTOMER_ORDER
  WHERE ORDER_TS IS NOT NULL
)
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, STATUS, TOTAL_AMOUNT
FROM LATEST_ORDER
WHERE RN = 1
ORDER BY CUSTOMER_ID;

PARTITION BY creates an independent numbering group for each CUSTOMER_ID. ORDER_TS chooses the latest business event. ORDER_ID resolves equal timestamps. The example assumes a larger ORDER_ID is the intended winner when timestamps tie; use that rule only if the application guarantees and accepts it. An identity value is not a substitute for event time when records can arrive late.

Latest N rows per group

Greatest-N-per-group is a small change: keep RN less than or equal to N. This query returns at most three orders per customer, not merely three orders total.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH ORDER_HISTORY AS
(
  SELECT CUSTOMER_ID,
         ORDER_ID,
         ORDER_TS,
         STATUS,
         ROW_NUMBER() OVER
         (
           PARTITION BY CUSTOMER_ID
           ORDER BY ORDER_TS DESC, ORDER_ID DESC
         ) AS RN
  FROM CUSTOMER_ORDER
  WHERE ORDER_TS IS NOT NULL
)
SELECT CUSTOMER_ID, ORDER_ID, ORDER_TS, STATUS
FROM ORDER_HISTORY
WHERE RN <= 3
ORDER BY CUSTOMER_ID, RN;

Use RANK instead of ROW_NUMBER when every order tied at the boundary must be returned. Be prepared for more than three rows in a customer group. Use DENSE_RANK when “three” means three distinct timestamps, including all orders at each timestamp.

The MAX-and-join-back alternative

An aggregate can find the greatest timestamp for each group, and a join can recover the other columns. This compact pattern is useful when all rows sharing the latest timestamp are valid answers.

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH MAX_TIME AS
(
  SELECT CUSTOMER_ID, MAX(ORDER_TS) AS MAX_ORDER_TS
  FROM CUSTOMER_ORDER
  WHERE ORDER_TS IS NOT NULL
  GROUP BY CUSTOMER_ID
)
SELECT O.CUSTOMER_ID, O.ORDER_ID, O.ORDER_TS, O.STATUS
FROM CUSTOMER_ORDER O
JOIN MAX_TIME M
  ON M.CUSTOMER_ID = O.CUSTOMER_ID
 AND M.MAX_ORDER_TS = O.ORDER_TS
ORDER BY O.CUSTOMER_ID, O.ORDER_ID;

This does not guarantee one row per customer. If two orders share MAX_ORDER_TS, both join back. That may be exactly right for “show all latest ties,” but it is wrong for a one-row customer summary. Trying to select STATUS beside MAX(ORDER_TS) in the grouped query is not a solution: STATUS is neither grouped nor aggregated, and even a permissive shortcut would not say which row supplied it.

Correlated NOT EXISTS pattern

Another accurate way to describe a latest row is “there is no better row in the same group.” It is verbose but makes the tie-break rule explicit.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT O.CUSTOMER_ID, O.ORDER_ID, O.ORDER_TS, O.STATUS
FROM CUSTOMER_ORDER O
WHERE O.ORDER_TS IS NOT NULL
  AND NOT EXISTS
      (
        SELECT 1
        FROM CUSTOMER_ORDER N
        WHERE N.CUSTOMER_ID = O.CUSTOMER_ID
          AND N.ORDER_TS IS NOT NULL
          AND (N.ORDER_TS > O.ORDER_TS
               OR (N.ORDER_TS = O.ORDER_TS
                   AND N.ORDER_ID > O.ORDER_ID))
      )
ORDER BY O.CUSTOMER_ID;

The optimizer can transform correlated SQL, but readable ROW_NUMBER is usually the clearest starting point. Compare access paths and measured workload performance rather than assuming one textual form is always fastest.

Indexing and access-path guidance

Top-N queries become expensive when Db2 must examine many qualifying rows and sort them before discarding nearly all of them. A useful candidate index puts columns used by equality predicates or grouping first, followed by ranking columns in the needed order. For the customer example, consider:

1
2
3
CREATE INDEX APP.IX_ORDER_LATEST
  ON APP.CUSTOMER_ORDER
     (CUSTOMER_ID, ORDER_TS DESC, ORDER_ID DESC);

This order groups a customer's entries together and places the newest keys first within the customer. It can help avoid or reduce sorting and can let Db2 find a small answer quickly. If the query filters one customer with CUSTOMER_ID = ?, the leading key is especially useful. For all customers, the OLAP operation can still require substantial work; the index is not a magic “one read per group” instruction.

• Keep catalog statistics current with the site's RUNSTATS strategy so Db2 can estimate groups, filtering, and ties.
• Include extra selected columns only after evaluating index size, update cost, and whether index-only access is valuable. A wide covering index is not free.
• Check EXPLAIN for matching index access, sort activity, estimated rows, and whether predicates are applied early.
• Clustering, table size, partitioning, buffer-pool behavior, and concurrent insert patterns can matter as much as the logical key list.
• FETCH FIRST is a result limit. OPTIMIZE FOR n ROWS is an access-path and retrieval hint; it does not replace the result limit.

Important correctness caveats

Nulls need an explicit policy

MAX ignores null inputs, while ordering can place nulls ahead of ordinary values in a descending sequence. That means a MAX-and-join query and an unfiltered ROW_NUMBER query can disagree. Decide whether a null timestamp means unknown, not yet occurred, or bad data, and write a predicate or null ordering that matches that decision.

A deterministic query can still observe changing data

A complete ORDER BY makes the winner deterministic for the rows visible to the statement. It does not freeze the table forever. A concurrent transaction can insert a newer row before the next execution. Your isolation level, commit timing, and application unit of work determine what is visible. For a stable report across several statements, consistency requirements must be designed beyond the ranking expression.

Pagination can skip or repeat changing rows

OFFSET plus FETCH FIRST is convenient, but a new row inserted ahead of the current page shifts later offsets. For a frequently changing ordered list, keyset pagination is usually more stable: remember the last ORDER_TS and ORDER_ID, then ask for rows below that complete key. Keep the same unique ordering on every page.

1
2
3
4
5
6
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TS, STATUS
FROM CUSTOMER_ORDER
WHERE ORDER_TS < :LAST_ORDER_TS
   OR (ORDER_TS = :LAST_ORDER_TS AND ORDER_ID < :LAST_ORDER_ID)
ORDER BY ORDER_TS DESC, ORDER_ID DESC
FETCH FIRST 25 ROWS ONLY;

If the sort columns can be null or can change after publication, the continuation predicate needs a documented policy. Keyset pagination improves deep-page access but does not create a historical snapshot by itself.

Do not confuse latest with current temporal data

System-period and business-time temporal tables can answer “as of” questions using temporal SQL. Manually choosing MAX(timestamp) answers a different question unless that timestamp is the approved validity boundary. Use temporal semantics when the table design provides them.

Explain It Like I'm Five

Imagine every class lines up for a race. FETCH FIRST says, “Let only five children through the gate.” ORDER BY says who stands at the front. ROW_NUMBER gives every child a different ticket. RANK lets tied children share a place and skips the next place. DENSE_RANK lets tied children share a place but does not skip a number. PARTITION BY creates a separate little race for every classroom. To pick one newest drawing from each classroom, number each classroom's drawings newest-first and keep ticket number one.

Exercises

1. Write a query for exactly five highest-paid employees. Make the answer deterministic when salaries tie, and explain why the tie-breaker belongs in ORDER BY.
2. Change the query to return everyone in the top five salary levels. Compare RANK and DENSE_RANK when the highest two employees have the same salary.
3. Return the two latest status-history rows for every ACCOUNT_ID using ROW_NUMBER. Choose and document a unique tie-breaker.
4. Rewrite the latest-status query with MAX(EVENT_TS) joined back to the history table. Insert two rows with the same maximum timestamp and describe the result.
5. Use EXPLAIN to compare the latest-order query before and after an index beginning with CUSTOMER_ID, ORDER_TS, and ORDER_ID. Record sort and access-path differences.
6. Design a null policy for missing event timestamps. Verify that the ROW_NUMBER and MAX-and-join forms return results consistent with that policy.