mbevilacqua
asked on
Top N query for all DBMS types
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
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
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
>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
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}
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}
BillBach - yes the shortcomings of the digital age. and they say cinemas are moving to digital screens...
ASKER
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?
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?
you could use something like hibernate that handles the different dialetcs for you.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...