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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Similar function from Dbase RecNo
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Clages1
Yak Posting Veteran

67 Posts

Posted - 12/28/2012 :  11:43:10  Show Profile  Reply with Quote
Hi , i have a table like this

Document Prod
12345 xxx
12345 yyy

123777 xxx
123777 zzz
123777 kkk


i would like to make a select and get this

Select document, Prod from table

and get this ( I dont have Column seq in the table)

Document Prod Seq
12345 xxx 1
12345 yyy 2

123777 xxx 1
123777 zzz 2
123777 kkk 3

is there a easy way to do this?
i am using SQL2008

Tks
Clages


Clages1
Yak Posting Veteran

67 Posts

Posted - 12/28/2012 :  11:54:38  Show Profile  Reply with Quote
I found this soluction, but too slow with big table
is there another way?
tks
Clages
select OD.OrderID, LineNumber, OD.ProductID, UnitPrice, Quantity, Discount
from Northwind.dbo.[Order Details] OD
join
(select count(*) LineNumber,
a.OrderID, a.ProductID
from Northwind.dbo.[Order Details] A join
Northwind.dbo.[Order Details] B
on A.ProductID >= B.ProductID
and A.OrderID = B.OrderID
group by A.OrderID, A.ProductID) N
on OD.OrderID= N.OrderID and
OD.ProductID = N.ProductID
where OD.OrderID < 10251
order by OD.OrderID, OD.ProductID

OrderID LineNumber ProductID UnitPrice Quantity Discount
----------- ----------- ----------- --------------------- -------- ---------------
10248 1 11 14.0000 12 0.0
10248 2 42 9.8000 10 0.0
10248 3 72 34.8000 5 0.0
10249 1 14 18.6000 9 0.0
10249 2 51 42.4000 40 0.0
10250 1 41 7.7000 10 0.0
10250 2 51 42.4000 35 0.15000001
10250 3 65 16.8000 15 0.15000001
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/28/2012 :  12:11:00  Show Profile  Reply with Quote
SELECT Document,Prod
,[SEQ] = row_number() over (partition by Document order by Prod)
FROM table

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  12:36:29  Show Profile  Reply with Quote
quote:
Originally posted by jimf

SELECT Document,Prod
,[SEQ] = row_number() over (partition by Document order by Prod)
FROM table

Jim

Everyday I learn something that somebody else already knew



This will not work in SQL 2000

Do like this. If you have identity PK you don't need to create identity column.

declare @t table (document int,prod Varchar(10))
insert @t select 12345,'xxx'
insert @t select 12345,'xxx'
insert @t select 123777,'xxx'
insert @t select 123777,'zzz'
insert @t select 123777,'kkk'


Select ID = Identity(int,1,1) ,* into #T
from @T t

select t.*,
(select Count(*)
        from    #T tt
        Where tt.document = t.document
        and tt.ID < = t.ID
        )
from #T t

ID	document	prod	(No column name)
1	12345	xxx	1
2	12345	xxx	2
3	123777	xxx	1
4	123777	zzz	2
5	123777	kkk	3

Edited by - sodeep on 12/28/2012 12:48:15
Go to Top of Page

Clages1
Yak Posting Veteran

67 Posts

Posted - 12/28/2012 :  14:12:49  Show Profile  Reply with Quote
Jim, you kill the problem

I will use only with MS-SQL2008
tks
Clages
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  14:36:04  Show Profile  Reply with Quote
Did it work or not?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/28/2012 :  14:47:38  Show Profile  Reply with Quote
The op said he's using 2008, he just posted in the wrong place.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  15:15:32  Show Profile  Reply with Quote
quote:
Originally posted by jimf

The op said he's using 2008, he just posted in the wrong place.

Jim

Everyday I learn something that somebody else already knew



Yah. I think now it becomes important to ask OP " Are you using SQL 2000? before we provide help
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/29/2012 :  00:32:49  Show Profile  Reply with Quote
It is important to understand that row_number() is the row number of a query, not a table.

The DBASE RECNO() function is a pointer to the current row in a table, a concept that just does not exist in SQL Server.

There is no internal row number for a particular row in a table unless there is a key column defined for that row in the data, like an IDENTITY column.





CODO ERGO SUM
Go to Top of Page

Clages1
Yak Posting Veteran

67 Posts

Posted - 01/09/2013 :  07:04:51  Show Profile  Reply with Quote
for my needs works fine
i need just a sequence breaking by Order

i have a table with several orders in each order several products
since SQL2008 doesnt have rownumber, like recno(from dbase)
this query posted by JIM solved my problems

tks again
Carlos Lages
Dec
Brazil

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.14 seconds. Powered By: Snitz Forums 2000