ASP.NET SQL Server Select top N values but skip M results

Question

I'm working on an ASP.Net project to display information on a website from a database. I want to select the top 10 items from a news table but skip the first Item and I'm having some problem with it.

<asp:SqlDataSource ID="SqlDataSource1" 
                   runat="server" ProviderName="System.Data.SqlClient"
                   ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" 
                   SelectCommand="SELECT top 5 [id], 
                                               [itemdate], 
                                               [title], 
                                               [description], 
                                               [photo] 
                                  FROM [Announcements] order by itemdate desc">
</asp:SqlDataSource>

This is what I have so far but I can't find any info online about how to skip a record

What do you mean by skip the record? Do you simply want to return the bottom 9 of the top 10 records, and handle all of this in a SQL query? — spong, Apr 07 '10 at 21:16

score 9 · Answer 1 · answered Apr 07 '10 at 21:15

For SQL Server 2005+, use:

SELECT x.*
  FROM (SELECT t.id,
               t.itemdate,
               t.title,
               t.description,
               t.photo,
               ROW_NUMBER() OVER (ORDER BY t.itemdate) AS rank
          FROM ANNOUNCEMENTS t) x
 WHERE x.rank BETWEEN a AND b

But there are better means of implementing pagination, if that is really what you're after.

score 8 · Accepted Answer · answered Apr 07 '10 at 21:16

8

You could use a filter or do it in the SQL:

SELECT top 10 
      [id], 
      [itemdate], 
      [title], 
      [description], 
      [photo] 
    FROM [Announcements]
    WHERE id <> (select TOP 1 id from announcements order by itemdate desc)
    order by itemdate desc

Edit: I took "skip the first Item" literally. Maybe that's not what you meant?

answered Apr 07 '10 at 21:16

AaronLS

37,329
20
143
202

This query would yield catastrophic performance results if your data set gets to be in the thousands. For 100 records it would be an ugly solution but it would work. Anytime you put something in the where clause it will be executed as many times as records that have to be compared. In this case you would execute (select TOP 1 id from announcements order by itemdate desc) for each record on the table. Not to mention that this sub-select has an order by on the entire table which is expensive. – DidierDotNet Sep 25 '12 at 18:57
@DidierDotNet How would you improve performance, and how would you make this code more elegant(not ugly)? Note I consider uglyness and performance to be to different things. Sometimes you have elegant code the performs poorly or ugly code that performs well. Would be interested in hearing how you would make this code both more elegant, better performing, and meet the OP's requirements. – AaronLS Sep 26 '12 at 16:20
Put the (select TOP 1 id from announcements order by itemdate desc) in a join to the main table and compare against the result of the join. – DidierDotNet Sep 27 '12 at 21:48

score 1 · Answer 3 · edited May 23 '17 at 12:15

1

Take a look at the sql generated in the answer to this question efficient-way-to-make-paging-on-sql-server-2008

edited May 23 '17 at 12:15

Community

1
1

answered Apr 07 '10 at 21:15

Christoph

4,251
3
24
38

DidierDotNet · Answer 4 · 2012-09-27T22:03:07.347

0

DECLARE @topid int
select @topid = max(id) from announcements group by itemdate, id 

SELECT top 10 [id], [itemdate], [title], [description], [photo]      
FROM [Announcements] A
WHERE A.id <> @topid
order by itemdate desc

edited Sep 27 '12 at 22:03

answered Sep 27 '12 at 21:51

DidierDotNet

103
7

ASP.NET SQL Server Select top N values but skip M results

4 Answers4