Select X number of Rows 1

[ronnyjljr]

Hello,

I think this is a rather simple question but IBM's DB2 docs fail to answer anything without having to search through hours worth of material. So, I know how to select the top 5 rows, SELECT * FROM TABLE.FOOBAR FETCH TOP 5 ROWS, I think that is the syntax. How do I create a statment to say, select rows 50 through 100, or 103 through 150? or the bottom 10 rows?

Thanks a bunch,
Ron

typedef map<GiantX,gold, less<std::shortestpathtogold> > AwesomeMap;

 

[notadba]

Hi ronnyjljr,

To get a similar result to the sql server 'top' function, you can add "FETCH FIRST XXXX ROWS ONLY" at the end of your select statement - that has been valid since UDB 7.x (at least). Not sure that there is any any way to look at a range of rows unless you use some variation on rank over(), partition by logic.

Hope that was of some help. Looking forward to see if any one else actually has an answer to the question - would be useful to know.

 

[BrianTyler]

Hi Ron,

One way would be to retrieve the whole result set, assign row numbers and use an outer query to restrict the result rows.

e.g.
select
rowno
,f1 ,f2 ,f3
from
(select
rownumber () over (order by f1)as rwno
,f1 ,f2 ,f3
from
Tab1) as T1
where
f1 between 50 and 100;

In general, to find DB2 SQL information, use Graeme Burchall's excellent cookbook - it is redabale and accurate.

http://mysite.verizon.net/Graeme_Birchall/id1.html

Regards

Brian

 

[MarcLodge]

Brian,

I think that there's a typo or possibly two in your example. Should "as rwno" not be "as rowno"? And should "where f1 between 50 and 100" be "where rowno between 50 and 100"? Also should should "rownumber" be Row_Number"? Sorry to ask, but I can't check this SQL out (see below).

Ron,
In order to get first, last, middle, you will have to have an order by in order to make sense of the data returned, otherwise you will just get the data back from DB2 in a random like order. To get the first x rows, use notadba's solution. To get the last x rows, reverse the order by and use the same statement. To get the middle bunch, Brian's solution will work, but not on all platforms. I work on an OS390 mainframe and OLAP functions such as these are not available and never have been, much to my dismay!

In short, if you are working on a platform that does not support OLAP functions, then I think you will have to do it programatically.

Marc

 

[BrianTyler]

Marc,

Of course you are right - I should really read replies before sending them. The documentation says 'row_number', but 'rownumber' appears to work.

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

Everyone,

We must get into the habit of defining the platform on which we work.

Regards to all

Brian

 

[ronnyjljr]

Hi Guys,


Thanks for all the quick responses, I did some tinkering with Brian's suggestion and it worked. I am posintg my actual SQL code that worked in the DB2 Control Center's query box so that it may help someone else in the future.

This is a table called GLOBALATTRIBUTE.LOG and I am returning all of the fields between rows 12 and 15 (for example). My ENTRY column is an integer that is unique and increases numerically as entries are added (just in case someone was wondering what the deal was with 'order by ENTRY').

Also to clarify, I am not familiar with creating variables on the fly, but rowno is in fact a temporary variable used to keep track of the row number.

[blue]

select rowno, ENTRY, DATE, USERNAME, LEVEL, SUBJECT, MESSAGE from (select Row_Number() over (order by ENTRY) as rowno, ENTRY, DATE, USERNAME, LEVEL, SUBJECT, MESSAGE from GLOBALATTRIBUTE.LOG) as T1 where rowno between 12 and 15;
[/blue]

Thanks for all the help!
-Ron

typedef map<GiantX,gold, less<std::shortestpathtogold> > AwesomeMap;

 

[MarcLodge]

Congrats Ron, and well done Brian for your great solution - I just wish I could do this on my platform!

Marc

 

[PruSQLer]

And if you don't have OLAP capability, this Solution works anywhere:


select org_id
From org o1
Where 50 < (Select count(*)
From org o2
where o1.org_id > o2.org_id)
and 100 >= (select count(*)
From _org o2
Where o1.org_id > o2.org_id)
Order by org_id