June 11, 2008 at 5:59 am
Is it possible to add a sequence number to rows such that it resets on the start of each grouping? For example, using the following code:
create table myTable (sales_person varchar(10), model varchar(10), date_sold datetime)
insert into myTable (sales_person, model, date_sold)
values ('David','A', cast('2008-01-01' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('David','A', cast('2008-01-02' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('David','A', cast('2008-01-03' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('Fred','A', cast('2008-01-01' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('Fred','A', cast('2008-01-04' as datetime))
insert into myTable (sales_person, model, date_sold)
values ('Fred','A', cast('2008-01-06' as datetime))
select * from myTable
drop table myTable
I would like the results to be:
1, David, A, 2008-01-01
2, David, A, 2008-01-02
3, David, A, 2008-01-03
1, Fred, A, 2008-01-01
2, Fred, A, 2008-01-04
3, Fred, A, 2008-01-06
Note that the first column (the sequence number) is reset upon each group being the sales_person and potentially the model in this example.
Thanks
June 11, 2008 at 6:09 am
Please give a look at the "ranking" functions such as "row_number()" and "rank()" in books online. Pay particular attention to use of the OVER() clause in conjunction with both the PARTITION BY and ORDER BY clauses.
Kent
June 11, 2008 at 6:58 am
Hi Kent,
Thanks for your help, I've managed to get it work using the ROW_NUMBER and partition by clause.
June 11, 2008 at 7:06 am
Well done. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply