Recipe11.2.Skipping n Rows from a Table


Recipe 11.2. Skipping n Rows from a Table

Problem

You want a query to return every other employee in table EMP; you want the first employee, third employee, and so forth. For example, from the following result set:

 ENAME -------- ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD 

you want to return:

 ENAME ---------- ADAMS BLAKE FORD JONES MARTIN SCOTT TURNER 

Solution

To skip the second or fourth or n th row from a result set, you must impose order on the result set, otherwise there is no concept of first or next, second, or fourth.

DB2, Oracle, and SQL Server

Use the window function ROW_NUMBER OVER to assign a number to each row, which you can then use in conjunction with the modulo function to skip unwanted rows. The modulo function is MOD for DB2 and Oracle. In SQL Server, use the percent (%) operator. The following example uses MOD to skip even-numbered rows:

 1  select ename 2    from ( 3  select row_number( ) over (order by ename) rn, 4         ename 5    from emp 6         ) x 7   where mod(rn,2) = 1 

MySQL and PostgreSQL

Because there are no built-in functions for ranking or numbering rows, you need to use a scalar subquery to rank the rows (by name in this example). Then use modulus to skip rows:

 1  select x.ename 2    from ( 3  select a.ename, 4    (select count(*) 5            from emp b 6            where b.ename <= a.ename) as rn 7     from emp a 8          ) x 9   where mod(x.rn,2) = 1 

Discussion

DB2, Oracle, and SQL Server

The call to the window function ROW_NUMBER OVER in inline view X will assign a rank to each row (no ties, even with duplicate names). The results are shown below:

  select row_number( ) over (order by ename) rn, ename   from emp RN ENAME -- --------  1 ADAMS  2 ALLEN  3 BLAKE  4 CLARK  5 FORD  6 JAMES  7 JONES  8 KING  9 MARTIN 10 MILLER 11 SCOTT 12 SMITH 13 TURNER 14 WARD 

The last step is to simply use modulus to skip every other row.

MySQL and PostgreSQL

With a function to rank or number rows, you can use a scalar subquery to first rank the employee names. Inline view X ranks each name and is shown below:

  select a.ename,        (select count(*)           from emp b          where b.ename <= a.ename) as rn   from emp a ENAME               RN ----------  ---------- ADAMS                1 ALLEN                2 BLAKE                3 CLARK                4 FORD                 5 JAMES                6 JONES                7 KING                 8 MARTIN               9 MILLER              10 SCOTT               11 SMITH               12 TURNER              13 WARD                14 

The final step is to use the modulo function on the generated rank to skip rows.




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net