Best way to implement paging in queries

I would like to know the best way to implement paging in queries.

Currently, I am doing something like this:

SELECT
  columns
FROM
  (SELECT columns FROM table ORDER BY column)
WHERE
  rownum BETWEEN start AND end

Is开发者_开发知识库 this the best way to go about it?

In SQL Server I would use the following:

SELECT DISTINCT *
FROM (SELECT TOP (@lim) ROW_NUMBER() OVER (ORDER BY sometable.id ASC) AS row,
                                                                        sometable.id AS id,
                                                                        sometable.name as name
                    FROM sometable) as tratable
WHERE Row > @start AND Row <= @lim

Yes, that's the best way I know by far. If you are working with SQL Server, you may combine Row_Number and CTE together. Take a look at http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

and for MySQL

SELECT MyField
FROM sometable
LIMIT [offset], [pagesize]

of course you should replace [offset] and pagesize with appropriate values.