Solved

Returning only limited data from database.

Posted on 2002-06-03
7
1,454 Views
Last Modified: 2012-06-21
I am using java as programming language and PostgreSQL as my database.

Is it possible to request the database to return only a certain number of records (100 only) based on a Select Query ?


In other words, consider this example :
SELECT name, age, depname from EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dept_id = DEPARTMENT.id;

This returns 1500 records.

Is there a way to tell the RDBMS to return only first 100 records ? Also, can i ask it to return next 100 records etc ?

The reason i need this feature is that some of my SQL Queries might return 500,000 records. I wonder if the resultset object in java will be able to handle that much data !

(One thought : What is the behaviour in LDAP. Are such features supported there.)
0
Comment
Question by:Jitu
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 5

Expert Comment

by:kelfink
ID: 7051906
Sure.

SELECT * FROM mytable  ORDER BY mytable.id
LIMIT 10 OFFSET 20;

The OFFSET is optional, and can be used to select a portion of the data set after the first row.  THe example selects the 20th through 29th rows.
0
 
LVL 1

Author Comment

by:Jitu
ID: 7053200
Kelfink that was excellent :)

Do you know which databases support LIMIT/OFFSET feature in SQL. ?

What i am trying is to implement paging for a user on a report, the data for which comes from the database. Right not i am using postgres database, but in future we may use other databases.

Are there any severe performance implications to using this.
0
 
LVL 5

Accepted Solution

by:
kelfink earned 50 total points
ID: 7054386
I don't know much about performance impacts, but I would assume that the database does have to go through most of the work of processing rows 1..19, even if you just want to get back 20-29.

All the databases I use have some form of limit. Unfortunately they all have different implementations.

On MS Sql Server, the equivalent is : SELECT TOP N ...
On Oracle, you have to use : ...WHERE ROWNUM <= N
On mySql, you use LIMIT, but it comes at the end of the statement.  Unfortunately, ANSI never defined a routine way to pick and choose portions of the result set.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 54

Expert Comment

by:nico5038
ID: 7265845

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: kelfink  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 1

Author Comment

by:Jitu
ID: 7266584
Thanks. Sorry for the delay in getting back.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7266760
Thanks for finalizing the Q ;-)

Nic;o)
0
 

Expert Comment

by:partner0
ID: 7426667
In various RDBMS, you only have the TOP or equivalent (SQL Server) or none (Oracle), making me think that all the DB that implement a mechanism for selecting (n to n+x) like mySQL does select TOP n+x and then discard TOP n... Which is worth even that selecting TOP n+x from a performance standpoint...

In fact, how could RDBMS reach result n+1 without reaching result n first, unless adding a search condition?

The most honest are Oracle where you explicitly add search conditions to do that... No magic from a performance standpoint...

Maybe, if you have ressource consuming search conditions, a way to enhance perf is to perform paging on client side, by passing a list of all matching ID to the client at the begining, then retreiving information by subset of IDs, saving the load of re-compute all search contidions each time you page...
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Question About Creating Primary Key Constraints on Oracle Tables... 5 57
SQL SELECT query help 7 56
What's wrong with this T-SQL Foreign Key? 7 51
Mongo DB 18 62
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question