row number - 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

General SQL Server Forums

New to SQL Server Programming

row number

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-01-02 : 14:29:21

hi,

sample code:



create table Sale
(
 customerID int
,OrderID int
,date_ datetime
)

insert into Sale
		  select '123','5465363','2012/10/01'
union all select '123','5465363','2012/10/01'
union all select '124','6545344','2012/10/01'
union all select '124','6545348','2012/10/02'
union all select '124','6545353','2012/10/04'



select 
	 s1.customerID
	,s1.OrderID
	,s1.date_
	,row_number() over (partition by s1.CustomerID order by (select getdate())) as showOrder
	,showOrder2 =  (
					select count(CustomerID)
						from
							sale as s2							
					where
						s2.CustomerID = s1.CustomerID
					and s2.Date_ <= s1.Date_ 
					)
from sale as s1

In select statement on select list showOrder2 is old version of row_number. What I want is to recreate the same result as row_number function under alias showOrder, but without using row_number function.

I've started creating showOrder2 but it is not working correctly for duplicate rows.

thanks for your ideas.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-02 : 15:09:07

If dates are not unique just do this

Declare @T table
(
 customerID int
,OrderID int
,date_ datetime
)

insert into @T
		  select '123','5465363','2012/10/01'
union all select '123','5465363','2012/10/01'
union all select '124','6545344','2012/10/01'
union all select '124','6545348','2012/10/02'
union all select '124','6545353','2012/10/04'


Select identity(int,1,1)ID,* into #ROWNUMBER
from @T

select 
	 s1.customerID
	,s1.OrderID
	,s1.date_
	,row_number() over (partition by s1.CustomerID order by (select getdate())) as showOrder
	,showOrder2 =  (
					select count(*)
						from
							#ROWNUMBER as s2							
					where
						s2.CustomerID = s1.CustomerID
					and ID < = s1.ID
					)
from #ROWNUMBER as s1

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-01-02 : 15:20:50

thank you sodeep.

Any other idea without using identity insert into another table.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-02 : 16:00:50

Nope. there should be column which should uniquely identify the row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-03 : 00:53:47

if you've duplicate rows then you need to have a unique id before you generate a sequence number. thats why select INTO step with identity column which SOdeep showed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-01-03 : 01:20:36

visakh16,

thank you. I was more like thinking of creating tally table and join tally on all these duplicate rows in order to avoid select INTO statement.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-03 : 01:21:34

still you dont have a unique value to link tally table to which would again require an identity column to be introduced in main table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-01-03 : 06:18:06

slimt_slimt, when row_number() servers what you need, why do you want to use the old method?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources