getting data in parts
-
I wanted to know if it is possible to get the data in parts. I mean my application has many records in the table, so for a sql query like select * from table, there is lots of records to be read from the ResultSet. Is there any query like select (first 100) * from table then select (next 100) * from table . . . something like this ? please help
-
I wanted to know if it is possible to get the data in parts. I mean my application has many records in the table, so for a sql query like select * from table, there is lots of records to be read from the ResultSet. Is there any query like select (first 100) * from table then select (next 100) * from table . . . something like this ? please help
select top 100 records from table where id > @idThe first time in, pass in an @id of 0. Next time, pass in the maximum id of the previous results.Deja View - the feeling that you've seen this post before.
-
I wanted to know if it is possible to get the data in parts. I mean my application has many records in the table, so for a sql query like select * from table, there is lots of records to be read from the ResultSet. Is there any query like select (first 100) * from table then select (next 100) * from table . . . something like this ? please help
SELECT * FROM Table WHERE RowNumber Between @FirstRow AND @EndRowSincerely, Elina Life is great!!! Enjoy every moment of it! :-O
-
select top 100 records from table where id > @idThe first time in, pass in an @id of 0. Next time, pass in the maximum id of the previous results.Deja View - the feeling that you've seen this post before.
That's a clever idea. But it does not work all the time: Say you have to sort on a field - then the id's gets shuffled. For example go to dell's outlet listings page: http://outlet.us.dell.com/ARBOnlineSales/topics/global.aspx/arb/online/en/InventorySearch?c=us&cs=22&l=en&lob=DIM&MODEL_DESC=XPS%20210&s=dfh[^] I wonder if they're doing the same thing here. Or are they just pulling all the record at once and doing the paging and sorting thing at the webpage level.
-
That's a clever idea. But it does not work all the time: Say you have to sort on a field - then the id's gets shuffled. For example go to dell's outlet listings page: http://outlet.us.dell.com/ARBOnlineSales/topics/global.aspx/arb/online/en/InventorySearch?c=us&cs=22&l=en&lob=DIM&MODEL_DESC=XPS%20210&s=dfh[^] I wonder if they're doing the same thing here. Or are they just pulling all the record at once and doing the paging and sorting thing at the webpage level.
You are right that it is a simplistic solution. I find that it fits a lot of cases though. A lot of time when you sort/shuffle the id's around, this is done by retrieving the data in one go and performing the sorting/shuffling on the server.
Deja View - the feeling that you've seen this post before.
-
SELECT * FROM Table WHERE RowNumber Between @FirstRow AND @EndRowSincerely, Elina Life is great!!! Enjoy every moment of it! :-O
I still didnt get what to do How should the above command be written as ? my table name is 'orders' and there are only 2 fields in that 'ORDERID' and 'ITEMDESIRED' There is no primary key as such. Yes I can make 'ORDERID' the Primary key though. What should be the '@FirstRow' and '@EndRow' values be?? or should I write them just like that. Please help, its urgent. I am using the ojdbc14.jar to connect to the Oracle 10g database. I read somewhere about the LIMIT and OFFSET command, but tried it in Oracle SQL* Plus and saw that it does not work. Also it gave the same exception when tried to run the command through my Java program. The command I ran is
select * from orders limit 10;and the Exception I got isORA-00933: SQL command not properly ended -
I still didnt get what to do How should the above command be written as ? my table name is 'orders' and there are only 2 fields in that 'ORDERID' and 'ITEMDESIRED' There is no primary key as such. Yes I can make 'ORDERID' the Primary key though. What should be the '@FirstRow' and '@EndRow' values be?? or should I write them just like that. Please help, its urgent. I am using the ojdbc14.jar to connect to the Oracle 10g database. I read somewhere about the LIMIT and OFFSET command, but tried it in Oracle SQL* Plus and saw that it does not work. Also it gave the same exception when tried to run the command through my Java program. The command I ran is
select * from orders limit 10;and the Exception I got isORA-00933: SQL command not properly endedThe example query I gave works fine for SQL Server 2005 @FirstRow = row, from which you want to start getting the results @EndRow = last row of your results In your case, for exapmple, it can be
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID BETWEEN 5 AND 25OR, if this does not work, in your db, try:
SELECT OrderID,ItmDesired
FROM ORDERS
WHERE OrderID > 5 AND ORDERID < 25Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O