SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 row number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 01/02/2013 :  14:29:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/02/2013 :  15:09:07  Show Profile  Reply with Quote
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

Edited by - sodeep on 01/02/2013 15:09:20
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 01/02/2013 :  15:20:50  Show Profile  Reply with Quote
thank you sodeep.

Any other idea without using identity insert into another table.

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/02/2013 :  16:00:50  Show Profile  Reply with Quote
Nope. there should be column which should uniquely identify the row.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2013 :  00:53:47  Show Profile  Reply with Quote
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/

Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 01/03/2013 :  01:20:36  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2013 :  01:21:34  Show Profile  Reply with Quote
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/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/03/2013 :  06:18:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000