How can SELECT statement return different groups by row number?

I wasn’t sure what the title would be, so apologies beforehand.

Let’s say I have a simple TSQL statement that looks like SELECT City from Cities. This returns one result with 26 rows:

  • Cross Join 'n' times a table
  • SQL Summarising Data
  • Select last records only if a certain condition is met
  • Introducing FOREIGN KEY constraint 'c_name' on table 't_name' may cause cycles or multiple cascade paths
  • SQL Server Cyrillic Writing '?????'
  • sp_setapprole and creating users / logins
  • City
    New York
    Los Angeles
    Chicago
    Houston
    Philadelphia
    Phoenix
    San Antonio
    San Diego
    Dallas
    San Jose
    Austin
    Indianapolis
    Jacksonville
    San Francisco
    Columbus
    Charlotte
    Fort Worth
    El Paso
    Memphis
    Seattle
    Denver
    Washington
    Boston
    Nashville
    Baltimore
    Oklahoma City
    

    Now, let’s say that I want to divide this result into 3 groups of 10 rows each. In other words, when I run the query, the result will display three “different” rows sets, the first two with 10 rows and the last one with the remainder of 6. It’s as if I had run these 3 queries at once: a top(10), a middle(10), and a What’s Left. If I were to run this query from a .Net app, the dataset would have 3 datatables.

    The issue here is that there’s nothing I can use to group the data. Even if I did, I don’t want to. I want to specify how many datatables this query will return and how many rows each datatable will have.

    5 Solutions collect form web for “How can SELECT statement return different groups by row number?”

    I think you want to use row-number() and some arithmetic:

    select ((seqnum  - 1) / 10) as grp, city
    from (select city, row_number() over (order by (select NULL)) as seqnum
          from cities
         ) c;
    

    Note that the ordering is not guaranteed. You really need a specific column to specify the order, because SQL tables represent unordered sets.

    EDIT:

    It is unclear exactly what you want to do. This divides the rows into groups of 10, which seems to be what you want to do. Of course, this returns only one result set — any SQL query only returns one result set.

    If you are using SQL Server 2012 or above you can use the OFFSET/FETCH keywords:

    So this would get your first 10 cities:

    SELECT City
    FROM Cities
    ORDER BY City
        OFFSET 0 ROWS
        FETCH NEXT 10 ROWS ONLY
    

    And this would get the next 10:

    SELECT City
    FROM Cities
    ORDER BY City
        OFFSET 10 ROWS
        FETCH NEXT 10 ROWS ONLY
    

    Also you can substitute these numbers with variables:

    DECLARE @PageSize INT = 10
    DECLARE @PageNumber INT = 5
    
    SELECT City
    FROM Cities
    ORDER BY City
        OFFSET @PageNumber * @PageSize ROWS
        FETCH NEXT @PageSize ROWS ONLY
    

    You need a Cursor or maybe a WHILE loop. I’ll flesh out the code when I’m on break in about 30 minutes, but for now suffice to say that you need to combine it with a windowing function to divide the set into groups of 10, and then select each group out 1-by-1.

    Summat like this:

    declare @paginator int = 0
    declare @totalPages int = (select count(*) from cities) / 10 + 1
    
    while @paginator <= @totalPages
    begin
        select city
        from (
            select 
                city, 
                row_number() over (order by city) as rownumber
          from cities)
        where rownumber <= @paginator * 10 + 10 
        AND rownumber > @paginator * 10
    
        set @paginator = @paginator + 1
    end
    

    I think the best solution would be a combination of loop and offset clause in a dynamic query. There it goes:

    create table city(
      name varchar(100)
    )
    
    insert into city values ('City')
    insert into city values ('New York')
    insert into city values ('Los Angeles')
    insert into city values ('Chicago')
    insert into city values ('Houston')
    insert into city values ('Philadelphia')
    insert into city values ('Phoenix')
    insert into city values ('San Antonio')
    insert into city values ('San Diego')
    insert into city values ('Dallas')
    insert into city values ('San Jose')
    insert into city values ('Austin')
    insert into city values ('Indianapolis')
    insert into city values ('Jacksonville')
    insert into city values ('San Francisco')
    insert into city values ('Columbus')
    insert into city values ('Charlotte')
    insert into city values ('Fort Worth')
    insert into city values ('El Paso')
    insert into city values ('Memphis')
    insert into city values ('Seattle')
    insert into city values ('Denver')
    insert into city values ('Washington')
    insert into city values ('Boston')
    insert into city values ('Nashville')
    insert into city values ('Baltimore')
    insert into city values ('Oklahoma City')
    
    DECLARE @TOTAL INT
            ,@GROUP INT
            ,@STR VARCHAR(10)
    
    SELECT 
        @GROUP=0
        ,@TOTAL=COUNT(*) 
    FROM CITY
    
    WHILE @GROUP < @TOTAL
    BEGIN
       SELECT @STR=CAST(@GROUP AS VARCHAR(10))
    
       EXEC('SELECT Name '+
            'FROM City '+
            '    OFFSET '+@STR+' ROWS '+
            '    FETCH NEXT 10 ROWS ONLY')
    
        SELECT @GROUP += 10;
    END
    

    Hope it helps!

    There is nothing native to any version of SQL server* that allows you to say “return N > 1 datasets based on the following criteria” in a single T-SQL statement. As per the (so far 3) other replies, you’d have to build N different and carefully constructed queries.

    (Ok, I have not worked with 2012 or 2014, but I’ll be greatly surprised–and downvoted–if they do have such syntax.)

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.