Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sequencing Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2008 5:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 5, 2012 9:12 AM
Points: 293, 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
Post #515092
Posted Wednesday, June 11, 2008 6:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, 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
Post #515098
Posted Wednesday, June 11, 2008 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 5, 2012 9:12 AM
Points: 293, Visits: 459
Hi Kent,

Thanks for your help, I've managed to get it work using the ROW_NUMBER and partition by clause.
Post #515128
Posted Wednesday, June 11, 2008 7:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, Visits: 467
Well done. :)
Post #515138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse