CODEWITHSUNDEEP
sqlsql-servert-sql
Joe_Hendricks
Joe_Hendricks

Reputation: 756

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:

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.

Upvotes: 1

Views: 101

Answers (5)

user4066358
user4066358

Reputation:

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!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 1

Jeff
Jeff

Reputation: 12785

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

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40319

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.)

Upvotes: -1

DavidG
DavidG

Reputation: 118987

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

Upvotes: 1

Related Questions