row number grouped by po - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

row number grouped by po

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-06 : 10:45:56

Good morning,

I'm sure there is a simple answer to this. I'm trying to increment a number but only by po.


select ap.Document, (select case when ap.Location = 'KN' then keenevalue 
					 else charlestownvalue end 
				     from PSoftCodes p
				     where CodeName = 'ap_intf_seq') + row_number() over(order by po) increment 
from Get_PS_AP_Info ap
WHERE Submission_Status is null
AND Status = 'POSTED'
and Location = 'kn'

what I want to see happen is this:


83684656	15615
33447ACR	15616
334477A	        15617
81094	        15618
81094	        15618
81094	        15618

What I actually get is this:


83684656	15615
33447ACR	15616
334477A	        15617
81094	        15618
81094	        15619
81094	        15620

Is there a way to group by a PO number in this case?

Thanks

Laura

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-06 : 10:50:21

select ap.Document,
( select case when ap.Location = 'KN' then keenevalue
else charlestownvalue
end
from PSoftCodes p
where CodeName = 'ap_intf_seq'
) + row_number() over ( partition by ap.Document order by po ) increment
from Get_PS_AP_Info ap
WHERE Submission_Status is null
AND Status = 'POSTED'
and Location = 'kn'

--------------------------
http://connectsql.blogspot.com/