Help!!!! URGENT

[pavi kavi]

Hi
I need to retrive only 20 datas everytime whenever i execute a querry even though the result set may contain 100 of rows i wnated this dynamically to happen.
For Example when i press the NEXT button it should take only from 21-40 & display it is it possible in EJB.
If YES please tell me how in EJB.
I am using JOIN statement
pavithra

[Peter den Haan]

This is not an EJB question, this is really an SQL question. Because, although you could filter out the unwanted records at any level in your application, there is only one "right" place to do it: the database query itself. I am assuming that you're either writing your own finder methods, or your application server allows you to put the following SQL in its generated finder methods.
Retrieving part of a larger result set, surely not an outlandish requirement, is suprisingly hard in standard SQL-92. If you have a query
SELECT a FROM b WHERE c ORDER BY d ASC
and you want only records n to m, you can use a nested select to count all the records that come before your current record, and impose that this count must be between m and n, like so:
SELECT outer.a FROM b outer WHERE outer.c AND
(SELECT COUNT(*) FROM b inner WHERE inner.c AND inner.d <= outer.d)) BETWEEN n AND m<br /> ORDER BY outer.d ASC<br /> Obviously, this is pseudocode, where a is a list of fields, b a list of tables, c your where clause, and d your order by which must specify a unique order. With a nested query like this, a database without a good optimiser will not perform very well. In fact, in such cases you may be better off running the entire query and throwing away the records you don't need.<br /> Fortunately, databases' proprietary SQL extensions usually address this problem. Oracle is probably the simplest because it has a ROWNUM pseudo-column which you can use directly:<br /> SELECT a FROM b WHERE c AND ROWNUM BETWEEN n AND m ORDER BY d ASC<br /> That's it. Microsoft SQL Server has a far cruder TOP modifier to the select, which means it is only useful if you already know the first record you want to show (denoted by first_d):<br /> SELECT top (m-n) a FROM b WHERE c AND d>=first_d ORDER BY d ASC
This is OK if you're going forward (just do d > last_d), but poses a problem when you want to browse backwards. Ok, you could change the sorting order I guess. I'm not familiar enough with other databases to comment on MySQL, Sybase and whatnot.
- Peter

[This message has been edited by Peter den Haan (edited May 03, 2001).]

[Anonymous]

Hi,
try this out.
SELECT Top 100 FROM EMP WHERE EMpNo > 1

[pavi kavi]

Hi peter
Thanks for help.
but is it possible with this querry
SELECT c.EDITION,c.PUBLICATIONDATE,d.NAME,d.SHIPPINGNOTES,d.PUBLISHINGIDENTIFIERCODE,d.RETAILPRICE,e.NAME,g.LASTNAME,h.NAME
FROM KEYWORD a,PRODUCTKEYWORDREL b,BOOK c,PRODUCT d,FORMAT e,AUTHORPRODUCTREL f,AUTHOR g,PUBLISHER h
WHERE a.keyword LIKE '%java%'
and a.keywordid=b.keywordid
and b.productid=c.productid
and b.productid=d.productid
and d.formatid=e.formatid
and b.productid=f.productid
and f.authorid=g.authorid
and c.publisherid=h.publisherid
can i use u r funda here .
please help me out .
Ihope so
Thanks once again
pavithra

Originally posted by pavi kavi:
Hi
I need to retrive only 20 datas everytime whenever i execute a querry even though the result set may contain 100 of rows i wnated this dynamically to happen.
For Example when i press the NEXT button it should take only from 21-40 & display it is it possible in EJB.
If YES please tell me how in EJB.
I am using JOIN statement
pavithra

[Peter den Haan]

To retrieve records 10 to 19:

SELECT c.EDITION,c.PUBLICATIONDATE,
    d.NAME,d.SHIPPINGNOTES,d.PUBLISHINGIDENTIFIERCODE,
    d.RETAILPRICE,
    e.NAME,g.LASTNAME,h.NAME 
FROM KEYWORD a, PRODUCTKEYWORDREL b, BOOK c, PRODUCT d,
    FORMAT e, AUTHORPRODUCTREL f, AUTHOR g, PUBLISHER h
WHERE a.keyword LIKE '%java%' 
    AND a.keywordid=b.keywordid 
    AND b.productid=c.productid 
    AND b.productid=d.productid 
    AND d.formatid=e.formatid 
    AND b.productid=f.productid 
    AND f.authorid=g.authorid 
    AND c.publisherid=h.publisherid
    AND (
        SELECT COUNT(*)
        FROM KEYWORD a1, PRODUCTKEYWORDREL b1, BOOK c1, PRODUCT d1,
            FORMAT e1, AUTHORPRODUCTREL f1, AUTHOR g1, PUBLISHER h1
        WHERE a1.keyword LIKE a.keyword
            AND a1.keywordid=b1.keywordid 
            AND b1.productid=c1.productid 
            AND b1.productid=d1.productid 
            AND d1.formatid=e1.formatid 
            AND b1.productid=f1.productid 
            AND f1.authorid=g1.authorid 
            AND c1.publisherid=h1.publisherid
            AND b1.productid <= b.productid
    ) BETWEEN 10 AND 19
ORDER BY b.productid ASC

If you know (as in "database constraints") that every PRODUCTKEYWORDREL record has a BOOK, PRODUCT, FORMAT, AUTHORPRODUCTREL, AUTHOR, and PUBLISHER, you can optimise this by dropping everything from BOOK onwards from the nested query.
Again, if you've got Oracle you would not do it this way at all; you'd just add "AND ROWNUM BETWEEN 10 AND 19". If you've got SQL Server you would try to use TOP if possible.
- Peter

[This message has been edited by Peter den Haan (edited May 04, 2001).]

[pavi kavi]

hi peter
Thanks for u r help but its not working it is showing me all the values instead of only between 10 to 19
CAn u please help me out
pavithra

Originally posted by Peter den Haan:
[B]To retrieve records 10 to 19:

SELECT c.EDITION,c.PUBLICATIONDATE,
    d.NAME,d.SHIPPINGNOTES,d.PUBLISHINGIDENTIFIERCODE,
    d.RETAILPRICE,
    e.NAME,g.LASTNAME,h.NAME 
FROM KEYWORD a, PRODUCTKEYWORDREL b, BOOK c, PRODUCT d,
    FORMAT e, AUTHORPRODUCTREL f, AUTHOR g, PUBLISHER h
WHERE a.keyword LIKE '%java%' 
    AND a.keywordid=b.keywordid 
    AND b.productid=c.productid 
    AND b.productid=d.productid 
    AND d.formatid=e.formatid 
    AND b.productid=f.productid 
    AND f.authorid=g.authorid 
    AND c.publisherid=h.publisherid
    AND (
        SELECT COUNT(*)
        FROM KEYWORD a1, PRODUCTKEYWORDREL b1, BOOK c1, PRODUCT d1,
            FORMAT e1, AUTHORPRODUCTREL f1, AUTHOR g1, PUBLISHER h1
        WHERE a1.keyword LIKE a.keyword
            AND a1.keywordid=b1.keywordid 
            AND b1.productid=c1.productid 
            AND b1.productid=d1.productid 
            AND d1.formatid=e1.formatid 
            AND b1.productid=f1.productid 
            AND f1.authorid=g1.authorid 
            AND c1.publisherid=h1.publisherid
            AND b1.productid <= b.productid
    ) BETWEEN 10 AND 19
ORDER BY b.productid ASC

If you know (as in "database constraints") that every PRODUCTKEYWORDREL record has a BOOK, PRODUCT, FORMAT, AUTHORPRODUCTREL, AUTHOR, and PUBLISHER, you can optimise this by dropping everything from BOOK onwards from the nested query.
Again, if you've got Oracle you would not do it this way at all; you'd just add "AND ROWNUM BETWEEN 10 AND 19". If you've got SQL Server you would try to use TOP if possible.
- Peter

[This message has been edited by Peter den Haan (edited May 04, 2001).][/B]