I need to write a sql query to display top 25 records with a catch

Question

I need to write a query as follows which will be bound to a grid

select top 25 * from ErrTable Order by DateErrorad Desc

However, I need to write this query to return only 25 records at a time, but when a user clicks next it will display the next 25 most recent records from the db.

How could I accomplish this?

Answer 1

You can implement a 'paging' technique using ROW_NUMBER() as detailed in this post: http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

Answer 2

Ok, since I don't know what Database Server/engine but basically you will need a range (in your case 25) and a page number (e.g. 0 is first page, 1 for next page of 25 records, etc).

In MySQL, you can do this (using the LIMIT command)....

SELECT * FROM TABLE LIMIT START, RANGE;

Wher TABLE is your table name, START is your start index/range, e.g if you have record 0 - 24, you can set start = 25, to read the next 25 (which is where RANGE comes into play).

This is only available in MySQL, in DB2 it's different though. Find out who your DB server/engine handles pagination.

In DB2:

SELECT * FROM TABLE FETCH FIRST N ROW ONLY;

Where N is a numeric value.

Edit For MSSQL, you can see another related post:

• How to implement LIMIT with Microsoft SQL Server?

Here's an article that shows Paging in ASP.NET.

Answer 3

You can use the LIMIT command to pick entries in a given range. However, I'm not sure if all engines support it, so a more general solution (albeit less efficient, I reckon), is...

SELECT TOP 25 *
FROM YOURTABLE
WHERE IDCOL NOT IN (SELECT TOP 25 * FROM YOURTABLE)

Sorry for the loose definition, I'm leaving and can't answer in more detail.

Answer 4

In MySQL you can do that with LIMIT, in MSSQL i don't know if that works.

SELECT * FROM table LIMIT 10

or

SELECT * FROM table LIMIT 0, 10 

This will display the first 10 results from the database.

SELECT * FROM table LIMIT 5, 5 

This will show records 6 to 10

Answer 5

Assuming you're using SQL Server (based on the ASP.NET tag):

declare @offset int
set @offset = 25

select * from (
   select *, row_number() over (order by DateErrorad desc) as i from ErrTable
) a
where i <= @offset + 25 and i > @offset

I would highly recommend you use an ORM, though. I love LINQ-to-SQL (it's a perfect complement to ASP.NET), and with that you could do this as:

var rows = Errors.Skip(offset).Take(25);

Answer 6

I guess it depends on your DBMS, the following LINQ2SQL-query (table DatabaseLogs from AdventureWorks)

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;
    var qq3 = context.DatabaseLogs.Skip(20).Take(10).ToList();
}

generates the following query for MSSQL

SELECT [t1].[DatabaseLogID], [t1].[PostTime], [t1].[DatabaseUser], [t1].[Event], [t1].[Schema] AS [Schema], [t1].[Object], [t1].[TSQL], [t1].[XmlEvent]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[DatabaseLogID], [t0].[PostTime], [t0].[DatabaseUser], [t0].[Event], [t0].[Schema], [t0].[Object], [t0].[TSQL]) AS [ROW_NUMBER], [t0].[DatabaseLogID], [t0].[PostTime], [t0].[DatabaseUser], [t0].[Event], [t0].[Schema], [t0].[Object], [t0].[TSQL], [t0].[XmlEvent]
    FROM [dbo].[DatabaseLog] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [20]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

Answer 7

by creating a stored procedure and passing your range

create procedure dbo.SelectWindow
@start int, @end int
as
begin

select *
from
(
select
*,
row_number() (order by ID) as Row
from dbo.table
) a
where Row between @start and @end


end
go