SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sequencing


Sequencing

Author
Message
David-155102
David-155102
SSC Eights!
SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)

Group: General Forum Members
Points: 897 Visits: 459
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
Kent Waldrop
Kent Waldrop
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 Visits: 467
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
David-155102
David-155102
SSC Eights!
SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)

Group: General Forum Members
Points: 897 Visits: 459
Hi Kent,

Thanks for your help, I've managed to get it work using the ROW_NUMBER and partition by clause.
Kent Waldrop
Kent Waldrop
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 Visits: 467
Well done. Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search