Solved

help with SQl 2005

Posted on 2011-10-01
5
209 Views
Last Modified: 2012-05-12
Can someone help / advise.
I have a tabe containing order line details. In it is an order line number.
i.e

Order    Line-Number
1234            1000
1234            1500
1234            2000
2222            1000
2222            2000

Because I would like to identify the line number in a more specfic way I'd like to add a counr to the table as follows

Order    Line-Number   Line Sequence
1234            1000                   1
1234            1500                   2
1234            2000                   3
2222            1000                   1
2222            2000                   2

Any ideas
0
Comment
Question by:EWHTLC
5 Comments
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 150 total points
Comment Utility
Hi,

use  Row_Number function.

http://msdn.microsoft.com/en-us/library/ms186734.aspx


- Bhavesh
SELECT OrderNo, Line, ROW_NUMBER() OVER(ORDER BY OrderNo) AS 'Row'

FROM TableName

Open in new window

0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
Comment Utility
Another way, compatible with SQL 2000:

SELECT t1.OrderNo, t1.LineNo, COUNT(T2.OrderNo) AS Sequence
FROM SomeTable t1 INNER JOIN
    SomeTable t2 ON t1.OrderNo = t2.OrderNo AND t1.LineNo >= t2.LineNo
GROUP BY t1.OrderNo, t1.LineNo
ORDER BY t1.OrderNo, t1.LineNo

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
Comment Utility
Actually the query using ROW_NUMBER should be:
SELECT  [Order],
        [Line-Number],
        ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY [Line-Number]) [Line Sequence]
FROM    YourTableName

Open in new window

0
 

Author Closing Comment

by:EWHTLC
Comment Utility
THank you all for you responses.
Sorry I was a while responding myself.

I tried all and although all worked pretty well the last option fit my needs perfectly.
I've split the points.
hope thats OK.

Thanks again
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Glad to help :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now