Sequence in select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Sequence in select

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-14 : 13:29:20

Joe writes "As part of a select, for an export, I need to create a sequence no for all those records with the same grouping field.

e.g.
select AccountCode,
SequencNo
from Transactions

Expected Results
AccountCode SequenceNo
----------- ----------
AAA 001
AAA 002
AAA 003
BBB 001
BBB 002
CCC 001

I could use a temp table and cursor to provide the expected results however I am concerned about performance and was wondering if there is a clever way of doing this with sub queries and identity fields"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-14 : 18:44:34

I'll suggest u a way, just give a try [I didn't test]

1. Use the following concept to number the records.

USE Pubs
SELECT 
COUNT(e1.emp_id) as [record #], e1.emp_id 
FROM employee AS e1 
INNER JOIN employee AS e2
ON e1.emp_id >= e2.emp_id
group by e1.emp_id 
order by e1.emp_id

2. Use a function to have some similar code as above 1

3. Call that function as something similar to the following
Select emp_id , RecordNumFn( emp_id) from employee

-- The above is conceptual. so just give a try.
-- The hardest part I guess is the Function

Also with ur data, I got some useful info from the following query
SELECT Count(*), e1.F1
FROM (select * from T ) AS e1
left JOIN (select * from T where F1 = 'AAA' ) AS e2 ON e1.F1 <= e2.F1
group by e1.F1
order by e1.F1

Play with that as well.

Can use cursor methods, which r not recommended by gurus of this forum.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-14 : 21:32:42

The code below shows two different methods.

I prefer the method that does not use the correlated subquery. I think it will scale better with large sets of data, and is easier to program when getting a sequence based on multiple columns.


--Declare table to hold test data
declare @t table (
AccountCode	varchar(20)	not null,
Seq	int	not null identity(1,1) ,
primary key clustered (AccountCode,Seq)
)


-- Load the test data
insert into @t ( AccountCode )
select
	-- Top used to make sure data is in order
	top 100 percent
	a
from
	(
	select	a ='AAA' union all	select	'AAA' union all
	select	'AAA' union all	select	'BBB' union all
	select	'BBB' union all	select	'CCC'
	) a
order by 
	a


-- First method to get a sequence
print 'Using join returning min seq for each group'
select
	a.AccountCode,
	SequenceNo	= a.Seq-b.Min_Seq+1
from
	@t a
	join
	(
	select
		bb.AccountCode,
		Min_Seq = min(bb.Seq)
	from
		@t bb
	group by
		bb.AccountCode
	) b
	on a.AccountCode = b.AccountCode
order by
	a.AccountCode


-- Second method to get a sequence
print 'With a correlated subquery'
select
	a.AccountCode,
	SequenceNo	=
		(
		select
			count(*)+1
		from
			@t b
		where 
			a.AccountCode = b.AccountCode and
			a.Seq > b.Seq
		)
from
	@t a
order by
	a.AccountCode



Results:


(6 row(s) affected)

Using join returning min seq for each group
AccountCode          SequenceNo  
-------------------- ----------- 
AAA                  1
AAA                  2
AAA                  3
BBB                  1
BBB                  2
CCC                  1

(6 row(s) affected)

With a correlated subquery
AccountCode          SequenceNo  
-------------------- ----------- 
AAA                  1
AAA                  2
AAA                  3
BBB                  1
BBB                  2
CCC                  1

(6 row(s) affected)

CODO ERGO SUM

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-15 : 13:59:01

In SQL Server 2005, you can simply do this:
(using Michael's test data setup)

SELECT AccountCode, ROW_NUMBER() OVER (PARTITION BY AccountCode ORDER BY Seq ASC) AS SequenceNo FROM @t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-16 : 00:50:09

In MVJ's example, also try this

Select Accountcode,(select count(*) from @t where Accountcode=T.Accountcode and seq<=T.seq) as SequenceNo from @t T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources