need help writing a query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

need help writing a query

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-09-28 : 17:36:05

Hi,
I have a table with the below data:

Billnumber
11111
11111
11111
33333
33333
44444
44444
44444
44444
I want to create an additional field lineItem like below
BillNumber LineItem
11111 1
11111 2
11111 3
33333 1
33333 2
44444 1
44444 2
44444 3
44444 4

Could somebody help me to write this query? Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 00:27:03

It is not possible until you have other unique column
If you have then

Select BillNumber,(select count(*) from yourTable where BillNumber=T.BillNumber and UniqueCol<=T.UniqueCol) as LineItem from yourTable T

Madhivanan

Failing to plan is Planning to fail

sreeram84n
Starting Member

4 Posts

Posted - 2012-06-08 : 12:12:49

select BillNumber,
(select COUNT(*) from yourTable where yourTable .BillNumber<=t.BillNumber) as rowid from yourTable t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 12:18:39

if you're on sql 2005 or above you can use like


SELECT Billnumber,Seq AS LineItem
FROM
(
SELECT Billnumber,ROW_NUMBER() OVER (PARTITION BY Billnumber ORDER BY (SELECT NULL)) AS Seq
FROM table
)t


if sql 2000 this is one way
CREATE TABLE #temp
(
ID int IDENTITY(1,1),
Billnumber int
)
INSERT #Temp (Billnumber)
SELECT Billnumber
FROM table
ORDER BY Billnumber

SELECT Billnumber,
COALESCE((SELECT COUNT(*) FROM #Temp WHERE Billnumber = t.Billnumber AND ID < t.ID),0)+1 AS LineItem
FROM #temp t

DROP Table #temp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-10 : 17:28:13

quote:
Originally posted by sreeram84n

select BillNumber,
(select COUNT(*) from yourTable where yourTable .BillNumber<=t.BillNumber) as rowid from yourTable t

Maybe not the fastest code to perform, but very clever

thumbs up from me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources