Selecting specific rows using select command

[Mahesh S]

Hi

I would like to write a SQL select statement that would fetch rows
numbered 50 to 100.

Let me clarify, if i say "fetch first 10 rows only", it returns the
first 10 rows from the resultset. how do I get a specific subset of
rows from the result set if I give a start and end value. For example,
retreiving rows 50 to 100 from the resultset.

Cheers
Mahesh

[kavallin]

maybe something like this will work for you

db2 select * from employee fetch first 20 rows only

EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE
JOB
EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
------ ------------ ------- --------------- -------- ------- ----------
--------
------- --- ---------- ----------- ----------- -----------
000010 CHRISTINE I HAAS A00 3978 1965-01-01
PRES
18 F 1933-08-24 52750.00 1000.00 4220.00
000020 MICHAEL L THOMPSON B01 3476 1973-10-10
MANAGER
18 M 1948-02-02 41250.00 800.00 3300.00
000030 SALLY A KWAN C01 4738 1975-04-05
MANAGER
20 F 1941-05-11 38250.00 800.00 3060.00
000050 JOHN B GEYER E01 6789 1949-08-17
MANAGER
16 M 1925-09-15 40175.00 800.00 3214.00
000060 IRVING F STERN D11 6423 1973-09-14
MANAGER
16 M 1945-07-07 32250.00 500.00 2580.00
000070 EVA D PULASKI D21 7831 1980-09-30
MANAGER
16 F 1953-05-26 36170.00 700.00 2893.00
000090 EILEEN W HENDERSON E11 5498 1970-08-15
MANAGER
16 F 1941-05-15 29750.00 600.00 2380.00
000100 THEODORE Q SPENSER E21 0972 1980-06-19
MANAGER
14 M 1956-12-18 26150.00 500.00 2092.00
000110 VINCENZO G LUCCHESSI A00 3490 1958-05-16
SALESREP
19 M 1929-11-05 46500.00 900.00 3720.00
000120 SEAN O'CONNELL A00 2167 1963-12-05
CLERK
14 M 1942-10-18 29250.00 600.00 2340.00
000130 DOLORES M QUINTANA C01 4578 1971-07-28
ANALYST
16 F 1925-09-15 23800.00 500.00 1904.00
000140 HEATHER A NICHOLLS C01 1793 1976-12-15
ANALYST
18 F 1946-01-19 28420.00 600.00 2274.00
000150 BRUCE ADAMSON D11 4510 1972-02-12
DESIGNER
16 M 1947-05-17 25280.00 500.00 2022.00
000160 ELIZABETH R PIANKA D11 3782 1977-10-11
DESIGNER
17 F 1955-04-12 22250.00 400.00 1780.00
000170 MASATOSHI J YOSHIMURA D11 2890 1978-09-15
DESIGNER
16 M 1951-01-05 24680.00 500.00 1974.00
000180 MARILYN S SCOUTTEN D11 1682 1973-07-07
DESIGNER
17 F 1949-02-21 21340.00 500.00 1707.00
000190 JAMES H WALKER D11 2986 1974-07-26
DESIGNER
16 M 1952-06-25 20450.00 400.00 1636.00
000200 DAVID BROWN D11 4501 1966-03-03
DESIGNER
16 M 1941-05-29 27740.00 600.00 2217.00
000210 WILLIAM T JONES D11 0942 1979-04-11
DESIGNER
17 M 1953-02-23 18270.00 400.00 1462.00
000220 JENNIFER K LUTZ D11 0672 1968-08-29
DESIGNER
18 F 1948-03-19 29840.00 600.00 2387.00

20 record(s) selected.

now fetch row 15 to 20 .....

db2 select * from (select t.*,rownumber() over () as rn from employee
t) as
x where rn between 15 and 20

EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE
JOB
EDLEVEL SEX BIRTHDATE SALARY BONUS COMM RN

------ ------------ ------- --------------- -------- ------- ----------
--------
------- --- ---------- ----------- ----------- -----------
--------------------

000170 MASATOSHI J YOSHIMURA D11 2890 1978-09-15
DESIGNER
16 M 1951-01-05 24680.00 500.00 1974.00
15

000180 MARILYN S SCOUTTEN D11 1682 1973-07-07
DESIGNER
17 F 1949-02-21 21340.00 500.00 1707.00
16

000190 JAMES H WALKER D11 2986 1974-07-26
DESIGNER
16 M 1952-06-25 20450.00 400.00 1636.00
17

000200 DAVID BROWN D11 4501 1966-03-03
DESIGNER
16 M 1941-05-29 27740.00 600.00 2217.00
18

000210 WILLIAM T JONES D11 0942 1979-04-11
DESIGNER
17 M 1953-02-23 18270.00 400.00 1462.00
19

000220 JENNIFER K LUTZ D11 0672 1968-08-29
DESIGNER
18 F 1948-03-19 29840.00 600.00 2387.00
20

/Roger

[andyhe]

A good solution using rownum has already been posted, but I wonder if
this is necessary. Maybe with a good 'order by' clause your last 50 of
100 records can become the first 50 of I-don't-care-how-many records
and you can just use 'fetch first 50 rows' again.

[Mahesh S]

Well, the problem is, this table has say 1000 records or more.

When displaying the results on a web page, we are only displaying 25
entries per page and hence the result would be split into 1000/25
entries and there are options allowing the user to navigate to x*25 set
of results where x could be an entry number.

So, how do I randomnly jump to any subset of the results? Isnt there
any option like

select * from table fetch rows 50 to 100 only

or something similar.

Thanks
Mahesh

[ChrisC]

Mahesh S wrote:

Well, the problem is, this table has say 1000 records or more.

When displaying the results on a web page, we are only displaying 25
entries per page and hence the result would be split into 1000/25
entries and there are options allowing the user to navigate to x*25 set
of results where x could be an entry number.

So, how do I randomnly jump to any subset of the results? Isnt there
any option like

select * from table fetch rows 50 to 100 only

or something similar.

Thanks
Mahesh

Probably the best way to do this, if you have an orderable column, is
to use an order by with fetch first x rows. So, you could select:

select * from table order by orderable_column fetch first 25 rows only

the keep the value of the orderable_column for the last row returned,
and when you want the next 25, issue:

select * from table where orderable_column 'keptvalue' order by
orderable_column fetch first 25 rows only

and so on. If you don't have a column that you want to order by (or
there isn't an appropriate column that works for some reason), then
using row_number is the next best option:

select {column list} from (select {column list}, row_number() over() as
row_num from table) x where row_num between 26 and 50

Note that {column list} needs to be a nice list of columns. Also, you
can put an order by clause in the over() clause if you want an ordering
to the results like over(order by col_a desc).

This (row_number()) works just fine, and is probably what you are
asking for - it just isn't quite as efficient as the other option.

-Chris

[Gert van der Kooij]

In article <11**********************@51g2000cwl.googlegroups. com>,
ma********@gmail.com says...

Well, the problem is, this table has say 1000 records or more.

When displaying the results on a web page, we are only displaying 25
entries per page and hence the result would be split into 1000/25
entries and there are options allowing the user to navigate to x*25 set
of results where x could be an entry number.

So, how do I randomnly jump to any subset of the results? Isnt there
any option like

select * from table fetch rows 50 to 100 only

or something similar.

Thanks
Mahesh

You can use the row_number function for that. You can find a couple of
usefull examples in the SQL Cookbook which you can download from
http://mysite.verizon.net/Graeme_Birchall/id1.html