Skip to content
Tutorials Center
My learning

Topics

Lesson 125 of 125 ยท Practice and Review

Row Selection Patterns: Odd/Even, Every Nth, Last N and Random Rows

Select odd or even rows, every Nth row, the first half, the last N rows, the middle row and a random row in SQL, using ROW_NUMBER, NTILE and LIMIT.

Some questions are about where a row sits rather than what it contains. "Give me every other row." "Show the last five orders." "Pick one winner at random." "Split the list in half." Relational tables have no built-in notion of position, so none of these has a dedicated keyword. Each one is a small recipe built from ROW_NUMBER(), LIMIT and a little arithmetic.

This lesson collects the position-based patterns that show up most often in reports, interviews and sampling jobs. They all depend on one idea: a row only has a position once you have defined an order. Without an ORDER BY, "the first row" and "the last row" mean nothing, and the database may return a different answer tomorrow.

Step one: give every row a number

ROW_NUMBER() OVER (ORDER BY ...) assigns 1, 2, 3, ... to the rows in the order you choose. Almost every pattern below numbers the rows in a CTE and then filters on the number, because you cannot use a window function directly in WHERE.

SQL Number the rows ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Notice that rn and id happen to match here, but they are different things. An id can have gaps (deleted rows, rolled-back inserts), while rn is always a gapless 1..N sequence. That difference is the reason the first pitfall below exists.

Odd and even rows

Number the rows, then test the remainder after dividing by 2 with the modulo operator % (also written MOD(rn, 2) in most databases).

SQL Odd rows and even rows ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

For odd rows keep rn % 2 = 1; for even rows keep rn % 2 = 0. The rn <= 10 filter only keeps the output short.

Do not test id % 2 unless you really mean "rows whose id is odd". If rows 3 and 4 were deleted, id % 2 = 0 still returns the survivors with even ids, which is not "every second row". Number first, then take the remainder of the row number.

In SQL Server and Oracle the same idea works (rn % 2 in T-SQL, MOD(rn, 2) in Oracle). PostgreSQL and MySQL accept both spellings.

Every Nth row

Odd and even rows are the special case N = 2. For any N, keep the rows whose number is a multiple of N. Use rn % N = 1 to start with the first row, or rn % N = 0 to start with row N.

SQL Every 5th row ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The order here is the hire date, with id as a tie-breaker so two people hired on the same day always come out in the same order. A tie-breaker is not optional: without it, ROW_NUMBER() may number tied rows differently from one run to the next, and your "every fifth row" would shift.

This pattern is how you thin out a large time series for a chart, or pull a systematic sample of customers for a survey.

The first half and the second half

There are two honest ways to split a table in half. The first compares the row number with half the total row count. The second uses NTILE(2), which deals the rows into two buckets of (nearly) equal size.

SQL First half, second half ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

COUNT(*) OVER () puts the total next to every row without collapsing them (an empty OVER () means "the whole result"). With an odd number of rows the two methods differ slightly: rn <= total / 2 uses integer division and leaves the middle row in the second half, while NTILE(2) gives the extra row to the first bucket. Pick one and say which when you describe the result.

If you only need the first N rows, skip all of this and use ORDER BY ... LIMIT N (see LIMIT and OFFSET).

The last N rows

The database cannot read a table "from the bottom", but you can flip the order. There are two classic ways.

Flip, limit, flip back. Sort descending, take N rows, then sort those N rows back into the original order if you need them that way.

Number from the end. Number the rows with a descending order and keep numbers 1..N.

SQL The last 5 hires ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Both queries return the same five people. The first is shorter and lets the database stop early when an index matches the sort order; the second is more flexible because you can reuse from_end for "the last N per group" by adding PARTITION BY (see Ranking).

Avoid the old trick of "all rows except the first COUNT - N" (OFFSET (SELECT COUNT(*) ...)). It is slower, harder to read, and not allowed in every database.

The middle row (and the median position)

The middle row of N rows has number (N + 1) / 2 when N is odd. When N is even there are two middle rows: N / 2 and N / 2 + 1. One expression covers both cases with integer division: keep rows numbered (N + 1) / 2 and (N + 2) / 2. For odd N these are the same row, so you get one result; for even N you get two.

SQL The middle row or rows ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

That finds the people in the middle of the salary ranking. To get the actual median salary, average the one or two salaries returned (AVG(salary)), which is exactly what Percentiles and Medians does in more depth.

A random row, or a random sample

To pick rows at random, sort by a random number and take the first N. The function name differs by database:

Database Random number
SQLite RANDOM() (a huge signed integer)
PostgreSQL RANDOM() (0 to 1)
MySQL / MariaDB RAND()
SQL Server NEWID() (a random GUID; RAND() returns one value for the whole query)
Oracle DBMS_RANDOM.VALUE
SQL One random employee ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

ORDER BY RANDOM() LIMIT N is perfect for small tables, but it has to generate a random number for every row and sort them all, so on a table with millions of rows it becomes slow. For big tables use a cheaper approach:

  • Sample by position. Pick a random number between 1 and the row count and fetch that row with OFFSET. Fast with an index, but gaps and ties make the choice slightly uneven.
  • Sample by key. WHERE id >= (random id) ORDER BY id LIMIT 1 is very fast but favours rows that follow gaps.
  • Use the engine's sampler. PostgreSQL has TABLESAMPLE SYSTEM (1) and SQL Server has TABLESAMPLE (1 PERCENT), which read a random fraction of the table's pages. They are quick, but the result size varies and neighbouring rows tend to come together.

Random results cannot be unit-tested directly, so test the shape (exactly one row, the row exists) rather than the value.

Pitfalls to remember

  • Always define the order. ROW_NUMBER() OVER () with no ORDER BY numbers rows in whatever order the engine reads them.
  • Add a unique tie-breaker (usually the primary key) so ties cannot reshuffle between runs.
  • Number before you filter. A window function cannot appear in WHERE; put it in a CTE or subquery and filter outside.
  • Do not confuse id with position. Only ROW_NUMBER() is gapless.
  • Mind the cost. Numbering and sorting a huge table is a full scan; add a WHERE filter first, or index the sort column.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You want every second row of a table, starting with the first. Which condition on a ROW_NUMBER() column named rn does that?

    Rows 1, 3, 5, ... leave a remainder of 1 when divided by 2. rn % 2 = 0 keeps rows 2, 4, 6, ...
  2. 2. Why is WHERE id % 2 = 0 an unreliable way to select the even-numbered rows?

    Deleted or skipped ids leave holes. The row number is always 1..N without gaps, so it measures position, while id only identifies a row.
  3. 3. What is the simplest way to return the last 5 rows of a result in their original order?

    Sorting descending puts the last rows first, LIMIT keeps five of them, and an outer ORDER BY restores the original order.
  4. 4. A table has 10 rows. Which row numbers does rn IN ((total + 1) / 2, (total + 2) / 2) keep when the division is integer division?

    (10 + 1) / 2 is 5 and (10 + 2) / 2 is 6, so both middle rows are returned. With an odd count the two expressions give the same number.
  5. 5. Why can ORDER BY RANDOM() LIMIT 1 be slow on a huge table?

    Every row gets a random sort key and the whole set is sorted before the first row is taken. Sampling by key or TABLESAMPLE avoids that cost.

Summary

  • Position only exists after you pick an ORDER BY; add a unique tie-breaker so the numbering is stable.
  • Number rows with ROW_NUMBER() in a CTE, then filter on the number.
  • Odd rows: rn % 2 = 1. Even rows: rn % 2 = 0. Every Nth row: rn % N = 0.
  • Halves: rn <= total / 2 with COUNT(*) OVER (), or NTILE(2).
  • Last N: sort descending and LIMIT N (re-sort if needed), or number from the end.
  • Middle row(s): rn IN ((total + 1) / 2, (total + 2) / 2).
  • Random rows: ORDER BY RANDOM() LIMIT N (or RAND(), NEWID()), and use samplers on big tables.

Next: SQL Puzzles and Interview Patterns, where you'll practice more recipes like these.