Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Top N query for all DBMS types

Posted on 2007-11-14
9
Medium Priority
?
1,033 Views
Last Modified: 2012-05-05
I am looking for a JDBC sql call for a top N query that will work with any DBMS type.

How does one retrieve N first (or least) rows from a record set? For example, how does one find the top five highest-paid employees in a given department? This attached code snipper would work for Oracle, but not SQL Server

I am not looking for the SQL Server and DB2 equavolent, but insight into a cronic application SQL problem. How is this query written to perform well and be DBMS independent? Is the better to approach to code something in the java resultSet layer?

Cheers!
Michael
SELECT *
FROM   (SELECT   ROWNUM,
                 e.*
        FROM     emp e
        ORDER BY sal DESC)
WHERE  ROWNUM < 6

Open in new window

0
Comment
Question by:mbevilacqua
[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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20285647
SQL Server has no rownum and uses

SELECT   TOP 5
e.*
FROM     emp e
ORDER BY sal DESC

so I'm not sure how you would make that "work with any DBMS type" with a single statement pattern...
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20285681
in other words:

>How is this query written to perform well and be DBMS independent?
you don't

>Is the better to approach to code something in the java resultSet layer?
yes
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 20285819
This is a DBMS-specific answer as well:
1) With Pervasive.SQL V8, Pervasive PSQL v9, and Pervasive PSQL Summit v10, the same "SELECT TOP 5 * FROM ..." works.
2) With Pervasive.SQL 2000i, Pervasive.SQL 7, or Btrieve 6.15, this syntax does NOT work, as the TOP command is not supported in these older engines.

In short, if you don't want a DBMS-specific answer, then you can NOT do it within a SQL query, no matter what solution you are talking about.  This means that you'll need to ask the engine for the entire row set, and then throw away any elements beyond what you care about.

{comment edited: mbizup, Access ZAPE}
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:imitchie
ID: 20285881
BillBach - yes the shortcomings of the digital age. and they say cinemas are moving to digital screens...
0
 

Author Comment

by:mbevilacqua
ID: 20286863
The product is required to support Oracle, SQL Server, DB2, and Sybase. So the answer can be limited to approaches shared across these database management systems.

I am aware of the technique to do Statement.setMaxRows(int max) method to limit the size of the result set.  This has proven to significantly improve the application performance by limiting the number of rows returned in a large query but this still does not work for top n and pagination queries.

One approach we are pursuing is to obtain the database type from the driver connection and then call the DBMS specific TOP N query.

I am looking for others with similiar requirements to share their approach. What other approaches are there outside of using setMaxRows with order by clause?
0
 
LVL 92

Expert Comment

by:objects
ID: 20287993
you could use something like hibernate that handles the different dialetcs for you.
0
 

Author Comment

by:mbevilacqua
ID: 20291521
That is a insightful comment objects and perhaps the right solution. However, we abandoned the use of the hibernate ORM after running into serious performance issues in the DML and went to straight JDBC.
0
 
LVL 92

Accepted Solution

by:
objects earned 2000 total points
ID: 20293110
straight jdbc has no support for what you want, you'll need to implement it yourself.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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…
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses

636 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