March 16, 2012 at 3:27 am
I have a table with below details
OrderId, PersonId, Name, Product, Date
1, 1, AA, AA, 01/01/2012
2, 1, AA, AA, 01/01/2012
3, 2, AB, AA, 01/01/2011
4, 2, AB, AA, 01/01/2011
I want a select statement which generate rowid group by OrderId and PersonId
the result should be like
RankId, OrderId, PersonId, Name, Product, Date
1, 1, 1, AA, AA, 01/01/2012
2, 2, 1, AA, AA, 01/01/2012
1, 3, 2, AB, AA, 01/01/2011
2, 4, 2, AB, AA, 01/01/2011
Please help.
March 16, 2012 at 3:34 am
some more info on table columns
OrderId, PersonId, Name, Product, Date, SpoonId, Priority
1, 1, AA, AA, 01/01/2012, 2, 1
2, 1, AA, AA, 01/01/2012, 3, 2
3, 2, AB, AA, 01/01/2011, 5, 1
4, 2, AB, AA, 01/01/2011, 7, 2
Means if we do group by, all rows will be resulted.
March 16, 2012 at 3:55 am
Simple enough to get the output
create table #person (OrderId int , PersonId int, Name varchar(20), Product varchar(20), [Date] date)
insert into #person (OrderId, PersonId, Name, Product, [Date])
values (1, 1, 'AA', 'AA', '01/01/2012'),
(2, 1, 'AA', 'AA', '01/01/2012'),
(3, 2, 'AB', 'AA', '01/01/2011'),
(4, 2, 'AB', 'AA', '01/01/2011')
select row_number() over (partition by Personid order by orderid) as rankid, OrderId, PersonId, Name, Product, [Date]
from #person
drop table #person
March 16, 2012 at 4:09 am
Thank you.... this works
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply