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

Answer 1

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.

Answer 2

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?

Answer 3

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