How to add sequence number for groups in a SQL query without temp tables

Question

I've created a complex search query in SQL 2008 that returns data sorted by groups, and the query itself has paging and sorting functions in it, but rather than returning a set number of records based on the paging options, it needs to return a set number of groups (so the number of records will vary).

I'm currently doing this through the use of Temp Tables (the first temp table creates a list of the Groups that will be selected as part of the search, and then numbers them... and the second query joins this table to the actual search... so, it ends up running the search query twice).

What I'm looking for is a more efficient way to do this using some of the new functions in SQL 2008 (which wouldn't require the use of temp tables).

If I can get the data in a format like this, I'd be set...

Record  Group     GroupSequence
-------|---------|--------------
1       Chickens  1
2       Chickens  1
3       Cows      2
4       Horses    3
5       Horses    3
6       Horses    3

Any ideas on how to accomplish this with a single query in SQL 2008, without using temp tables?

Which columns are from data and which need the values calculated? — RichardTheKiwi
– RichardTheKiwi, Commented Mar 9, 2011 at 23:00
The first and second columns ("Record[Number]" and "Group") are from data. The column "GroupSequence" would be calculated by the query. — Jacob
– Jacob, Commented Mar 9, 2011 at 23:04

RichardTheKiwi · Accepted Answer · 2011-03-09 23:04:13Z

13

Sample data

create table sometable([group] varchar(10), id int, somedata int)
insert sometable select 'Horses', 9, 11
insert sometable select 'chickens', 19, 121
insert sometable select 'Horses', 29, 123
insert sometable select 'chickens', 49, 124
insert sometable select 'Cows', 98, 1
insert sometable select 'Horses', 99, 2

Query

select
    Record = ROW_NUMBER() over (order by [Group], id),
    [Group],
    GroupSequence = DENSE_RANK() over (order by [Group])
from sometable

Output

Record               Group      GroupSequence
-------------------- ---------- --------------------
1                    chickens   1
2                    chickens   1
3                    Cows       2
4                    Horses     3
5                    Horses     3
6                    Horses     3

answered Mar 9, 2011 at 23:04

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chezy525 · Accepted Answer · 2011-03-09 23:04:07Z

1

Without more details about the tables you have, I'd say look into CTE queries and the row_number function... something along the lines of:

;with groups as (
  select top 10 name, row_number() over(order by name) 'sequence'
  from table1
  group by name
  order by name
)
select row_number() over(order by g.name) 'Record',
  g.name 'GroupName',
  g.sequence 'GroupSequence'
from groups

answered Mar 9, 2011 at 23:04

chezy525

4,1946 gold badges33 silver badges42 bronze badges

2 Comments

Jacob Over a year ago

For the purposes of pagination, I've modified most of my search queries to use this new "with <table_name> as" notation, which does appear to run much faster than either @ or # temp tables.

chezy525 Over a year ago

Be careful, because that isn't always going to be true. The with query notation, in this usage, is like a named subquery (which can be used several times)... so sometimes, a temp table or table variable will be better options.

Collectives™ on Stack Overflow

How to add sequence number for groups in a SQL query without temp tables

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related