| 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
|
|
|
|
|
Similar function from Dbase RecNo