How to write a paged, sorted query that also returns group buckets

Question

Trying to write a query that returns paged/sorted results that also has grouping in buckets something like this:

0-9999, 10,000-99,999, 100,000-999,999, 1,000,000-49,999,999, 50,000,000+

Each should have a startIndex on where to jump to when they click on it

Below is the sample query to load this data. The user would see the groups at the bottom of the page, but they depend on what column they are sorting off of. Clicking on one of these groups would then jump the user to that page and index. In my case, there are many more columns, but I think the grouping comes down to either a string, number, or datetime. For the string, the UI wants each letter of the alphabet to be a group, but only those letters that have data. For everything else, there should be around 5 or 6 buckets.

Does anyone know how to do this? What is this even called? Couldn't find anything on the web, but I'm not sure what to even look for.

declare @startIndex int
declare @pageSize int
declare @Names table
(
    FirstName varchar(100),
    LastName varchar(100),
    Age int,
    NetWorth money
)

select @startIndex = 1
select @pageSize = 10

insert into @Names (FirstName, LastName, Age, NetWorth) values ('John', 'Smith', 24, 304050)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('John', 'Williams', 31, 430901)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Adam', 'Wilson', 29, 121000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Jen', 'Phillips', 75, 1450000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Tommy', 'John', 33, 99000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Alex', 'Smith', 48, 12800000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Dianne', 'Lane', 60, 94000000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Charles', 'Barkley', 46, 21500000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Will', 'Allen', 21, 4000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Michael', 'Jordan', 50, 94500000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Jenny', 'Block', 43, 509000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Oprah', 'Winfrey', 61, 55000000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Charles', 'Smith',50, 3400000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('LeBron', 'James', 29, 74678000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Chris', 'Paul', 30, 19400000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Kobe', 'Bryant', 38, 124600000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Antonio', 'Gates', 36, 17040190)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Tony', 'Romo', 35, 28403400)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Nancy', 'Cammo', 44, 167880)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Bill', 'Brown', 45, 98000)
insert into @Names (FirstName, LastName, Age, NetWorth) values ('Paul', 'Jones', 26, 987900);

WITH SearchResults AS 
(
        SELECT 
            'RowNum' = ROW_NUMBER() over(ORDER BY NetWorth asc)
            ,Age
            ,NetWorth
            ,FirstName
            ,LastName
        FROM @Names
        group by Age, NetWorth, FirstName, LastName
),
counts as (
    select 'TotalRows' = count(*) from @Names
),
maxvalue as (
    select 'Val' = max(NetWorth) from @Names
)
SELECT  sr.RowNum
        ,sr.FirstName
        ,sr.LastName
        ,sr.Age
        ,sr.NetWorth
        ,'TotalRows' = c.TotalRows
        ,'MaxValue' = mv.Val
FROM    SearchResults sr
cross apply counts c
cross apply maxvalue mv
WHERE   sr.RowNum BETWEEN @startIndex AND @pageSize - @startIndex + 1

/*
select  NetWorth,
        count(NetWorth) 
from    @Names
group by NetWorth
order by NetWorth
*/

Any help would be appreciated.

score 0 · Answer 1 · answered Nov 19 '14 at 20:23

Here's one attempt, I don't have sql-server (which I assume you are using) at hand so you may have to adjust the query slighly:

select * from (
    select ROW_NUMBER() over(ORDER BY NetWorth asc) as rn
         , Age, NetWorth, FirstName, LastName
         , count(*) over () as totalrows
         , max(networth) over () as max_value
    from names
) as t 
where rn between 1 and 10;

How to write a paged, sorted query that also returns group buckets

1 Answers1