Similar function from Dbase RecNo

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 2000 Forums

SQL Server Development (2000)

Similar function from Dbase RecNo

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2012-12-28 : 11:43:10

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

69 Posts

Posted - 2012-12-28 : 11:54:38

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-28 : 12:11:00

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 12:36:29

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

Clages1
Yak Posting Veteran

69 Posts

Posted - 2012-12-28 : 14:12:49

Jim, you kill the problem

I will use only with MS-SQL2008
tks
Clages

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 14:36:04

Did it work or not?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-28 : 14:47:38

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

Jim

Everyday I learn something that somebody else already knew

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 15:15:32

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-29 : 00:32:49

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

Clages1
Yak Posting Veteran

69 Posts

Posted - 2013-01-09 : 07:04:51

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources