May 14, 2012 at 4:09 am
Hi all.
I have tbl:
id, product
1, b
1, a
1, a
2, c
2, d
3, e
3, e
3, e
3, e
I want to use select statement to retrieve like that:
Counting condition (seq_count) is: id=lag(id) and product=lag(product)
id, product, seq_count
1, b ,1
1, a ,1
1, a ,2
2, c, 1
2, d, 2
3, e,1
3, e,2
3, e,3
3, e,4
..........
Thanks in advance.
May 14, 2012 at 4:17 am
Hi welcome to SSC, please could you take a moment to read the second link in my signature on how to post code as it will help us, help you faster, especially with more complex queries.
Based on the sample data it seems like a case for ROW_NUMBER, the only thing would be where ID = 2 and Product = d as you want seq_count = 2, but it would return a seq_count = 1, unsure if this is a typo of if it is correct. Also the ordering is done alphabetically so A will be over B.
declare @tbl table (id int, product char(1))
insert into @tbl (id, product) values (1, 'b'),
(1, 'a'),
(1, 'a'),
(2, 'c'),
(2, 'd'),
(3, 'e'),
(3, 'e'),
(3, 'e'),
(3, 'e')
SELECT
ID,
product,
ROW_NUMBER() OVER (PARTITION BY id,product ORDER BY id) as seq_count
FROM
@tbl
May 14, 2012 at 6:19 am
Thanks Anthony!
I have read the article, Forum Etiquette. I will be careful next time.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply