• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1500
  • Last Modified:

Returning only limited data from database.

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
Jitu
Asked:
Jitu
  • 2
  • 2
  • 2
  • +1
1 Solution
 
kelfinkCommented:
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
 
JituAuthor Commented:
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
 
kelfinkCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nico5038Commented:

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
 
JituAuthor Commented:
Thanks. Sorry for the delay in getting back.
0
 
nico5038Commented:
Thanks for finalizing the Q ;-)

Nic;o)
0
 
partner0Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now