custid with Seqno
-
Hi
I need query which would give results with custid with seqno
my table look like
custid
--------
101
101
101
102
102
104
104
and results would be like
101 - 1
101 -2
101 -3
102 - 1
102 - 2
104-1
104-2
i am using SQL-2000
Appreciate your earlier help on this
thanks
S.V
-
Here's a way to do it.
select
x.custid,
n.n
from (
select c1.custid,
count(*) as d,
( select count(*) from customer c2
where c2.custid < c1.custid ) as s
from customer c1
group by c1.custid
) x
join nums n
on n.n <= x.d
custid n
----------- -----------
101 1
101 2
101 3
102 1
102 2
104 1
104 2
(7 row(s) affected)
This method requires a numbers table (here called 'nums') with a column called 'n'
I've 'borrowed' this solution from one of Itzik's articles in SQL Server Mag May 2005 issue.
/Kenneth
-
So, why not post how to make a "Numbers" table or provide the actual link? Aren't we a full service shop?
Venkat... here's how to make the "Nums" table that Kenneth spoke of...
--===== Create and populate the Tally table on the fly SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Nums FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Nums ADD CONSTRAINT PK_Nums_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it GRANT SELECT ON dbo.Nums TO PUBLIC
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Also, be aware that this method uses a "Triangular Join" which can get very slow if the "triangles" are allowed to get to any size... they can be hundreds of times slower and more resource greedy than a cursor...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=359124&p=2
... but work pretty well on "small triangles" as you have stated in your code example (number of identical CustID's). Write back if the number of identical CustID's in your table are more than a dozen or so...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What? And rob you of the opportunity to show how to do it?
Full service we may provide, though the best path to learning isn't always to get the complete answers served on a plate (at least not all at once)
/Kenneth
-
Your solution can not work without the numbers table... at least a link would have been nice...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks to all for your soultion on this. I am also working on that and will back to you as soon as i got the solution for this
-
Yes, I know that. I wrote that you need a numbers table.
My aim isn't to always at all cost post 100% conclusive close-the-case posts.
I'm more interested in getting people to think, and awakening their curiosity.
Without questions, there's not much of a discussion left
/Kenneth
-
declare @sample table (custid int)
insert @sample
select 101 union all
select 101 union all
select 101 union all
select 102 union all
select 102 union all
select 104 union all
select 104
declare @stage table (rowid int identity, custid int)
insert @stage select custid from @sample order by custid
select s.custid, s.rowid - q.d + 1 as seq
from @stage as s
inner join (
select custid,
min(rowid) as d
from @stage
group by custid
) as q on q.custid = s.custid
SELECT CustID,
ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY CustID) AS Seq
FROM @Sample
N 56°04'39.16"
E 12°55'05.25" -
Much better... but won't work in SQL Server 2000 which is what this forum is...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My aim isn't to always at all cost post 100% conclusive close-the-case posts. I'm more interested in getting people to think, and awakening their curiosity. Without questions, there's not much of a discussion left Heh... and people bitched about "banker's rounding"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply