Geek Answers Handbook

I need to write a sql query to display the 25 best entries with a trick

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

select top 25 * from ErrTable Order by DateErrorad Desc

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

How could I do this?

+5
source share
7 answers

You can implement the swap technique using ROW_NUMBER (), as described in detail in this post: http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

+3
source

, , /, ( 25) (, 0 - , 1 - 25 ..)..

MySQL ( LIMIT)....

SELECT * FROM TABLE LIMIT START, RANGE;

Wher TABLE - , START - /, , 0 - 24, start = 25, 25 ( RANGE ).

MySQL, DB2 . , / DB .

DB2:

SELECT * FROM TABLE FETCH FIRST N ROW ONLY;

N - .

. MSSQL :

, ASP.NET.

+2

LIMIT . , , , ( , )...

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

, .

0

MySQL LIMIT, MSSQL , .

SELECT * FROM table LIMIT 10


SELECT * FROM table LIMIT 0, 10

10 .

SELECT * FROM table LIMIT 5, 5

6-10

0

, SQL Server ( ASP.NET):

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

ORM. LINQ-to-SQL ( ASP.NET), :

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

I think it depends on your DBMS, the next LINQ2SQL query (table DatabaseLogsfrom 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
0
source

by creating a stored procedure and passing a 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
0
source

More articles:


All Articles