There Are a Method to Paging Using Ansi SQL Only

There are a method to paging using ANSI SQL only?

See Limit—with offset section on this page: http://troels.arvin.dk/db/rdbms/

BTW, Firebird also supports ROWS clause since version 2.0

How to implement paging mechanism in generic way for SQL standards?

As far as I know there is no generic functionality to implement the pagining mechanism for the all the database.

The syntax to implement the pagination may also change with the database, so it is hard to say that there is a genric functionality to implement it across all the database.

You can refer There are a method to paging using ANSI Sql only? where the accepted answer refers to a link which says to use it like

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum,
    columns
  FROM tablename
) AS foo
WHERE rownum > skip AND rownum <= (n+skip)

How universal is the LIMIT statement in SQL?

http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows lists all of the major variants of the select command.

I believe the best way to do this is to use the SET ROWCOUNT command before your SELECT statement.

So, for you:

SET ROWCOUNT 1
SELECT %s FROM %s

How to achieve Custom Paging in SQL Pivot Query

For this you could amend your code to use OFFSET like this:

DECLARE @pageSize INT;
DECLARE @pageNumber INT;

SET @pageSize = 1000;
SET @pageNumber = 1;

SELECT my_columns_here
FROM dbo.my_table_here
ORDER BY my_ordered_column 
OFFSET @pageSize * (@pageNumber - 1) ROWS 
FETCH NEXT @pageSize ROWS ONLY;

On a separate note, I noticed you are using a classic JOIN:

SELECT blabla
FROM Attendace a, employee e
WHERE e.EmpID = a.EmpID

Most developers and dba's will use ANSI-92 as a standard these days:

SELECT blabla
FROM Attendace        a
  INNER JOIN employee e ON a.EmpID = e.EmpID

This way you tend to avoid accidental CROSS joins and unnecessary ambiguity by separating relation from filter logic.

JDBC Pagination

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rows and start from i-th item clauses directly to the SQL for it to be efficient. Depending on the database this might actually be quite easy (see MySQL's LIMIT -keyword), on other databases such as Oracle it can be a little trickier (involves subquery and using rownum pseudo column).

See this JDBC Pagination Tutorial:
http://java.avdiel.com/Tutorials/JDBCPaging.html

---

Related Topics