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

Question

I 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 instead of returning a given number of records based on the parameters of the search call, it needs to return a given number of groups (so the number of entries will be different).

I am currently doing this using Temp tables (the first temp table creates a list of groups to be selected as part of the search, and then their numbers ... and the second query joins this table with the actual search ... so that it ends execution search query twice).

I am looking for a more efficient way to do this using some of the new features in SQL 2008 (which do not require the use of temporary tables).

If I can get the data in this format, I would 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 temporary tables?

Answer 1

Data examples

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 

Request

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

Exit

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

Answer 2

Without additional information about the tables you have, I would say in CTE and row_number ... something like:

 ;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