Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Returning only limited data from database.

Posted on 2002-06-03
7
Medium Priority
?
1,487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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