CODEWITHSUNDEEP
c#sqlsql-serverazuresqldatareader
Dave Gordon
Dave Gordon

Reputation: 1835

sql select top X to X+N

I have the following code:

private ArrayList LoadContentObjects()
        {
            ArrayList tmp = new ArrayList();
            SqlCommand command = null;
            command = new SqlCommand("SELECT TOP 150 ObjectUrl FROM [ContenbtObjectsUnprocessed]");
            command.CommandType = System.Data.CommandType.Text;
            command.Connection = conn;
            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    var res = reader.GetString(0);
                    tmp.Add(res);
                }
            }

            reader.Close();

            return tmp;
        }

Which reads 150 rows from the table ContentObjectsUnprocessed

(must fix the spelling mistake in the table!) It then check is there are rows; if there is it adds them to a tmp ArrayList and returns that to the caller.

When I call this a method a second time. I would like it to load the next 150 rows. However, I have not been able to do that. This SqlConnection is to an Azure Sql Server.

So the Question:

  1. How do I get the Method provided to read the Next 'N' rows from the database?

Upvotes: 0

Views: 296

Answers (3)

Augusto Barreto
Augusto Barreto

Reputation: 3695

If you are using Sql Server 2012+ or (Azure) Sql Database, you can use OFFSET-FETCH arguments of the ORDER BY clause. Example:

SELECT [Id]
      ,[Name]
      ,[Location]
  FROM [dbo].[Company]
  ORDER BY Name
  OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY

Reference: http://msdn.microsoft.com/en-us/library/ms188385.aspx

Upvotes: 1

huseyin tugrul buyukisik
huseyin tugrul buyukisik

Reputation: 11920

select a.ObjectUrl 
from
  (
    SELECT ObjectUrl,ROW_NUMBER() OVER (order by ObjectDateMaybe) rn 
    FROM [ContenbtObjectsUnprocessed]
  ) a
where a.rn<=300 and a.rn>=150

You can cache blocks of 150 rows in different temporary tables for better performance.

Faster for reading, slower for deletion:

    SELECT ObjectUrl 
    FROM [ContenbtObjectsUnprocessed]
    where predefined150smultiples=2

where predefined150smultiples is a column which has 1,1,1,1,...2,2,2,2, for each group of 150 rows. Deleting needs an update of many rows so this may not be that good. As hvd commented, any update changing the order makes this column obsolete and needs a fresh update of whole table which is not good too.

Upvotes: 2

Brave Soul
Brave Soul

Reputation: 3620

if you are using SQL-Server 2012 then it will help you. use LIMIT

SELECT a.ObjectUrl FROM [ContenbtObjectsUnprocessed] a LIMIT 151,300

Upvotes: 1

Related Questions