Solved

# Line count by group

Posted on 2002-04-23
267 Views
For the life of me I can not remember how to do the following:

Given a table with the following:
ordr_sys_key_id, ordr_dt, locn_sys_key_id
1001, 2002/03/04, 305
1003, 2002/03/04, 305
1011, 2002/03/04, 305
1002, 2002/03/04, 306
1004, 2002/03/04, 306
1012, 2002/03/04, 306

I want ro produce something like this in a query:
1001 2002/03/04 305 1
1003 2002/03/04 305 2
1011 2002/03/04 305 3
1002 2002/03/04 306 1
1004 2002/03/04 306 2
1012 2002/03/04 306 3

Thanks,
Darek

0
Question by:dtomyn
• 2

LVL 69

Expert Comment

ID: 6963862
Something like this should do it:

SELECT ordr_sys_key_id, ordr_dt, locn_sys_key_id,
(SELECT COUNT(*) + 1
FROM tablex x2
WHERE x1.locn_sys_key_id = x2.locn_sys_key_id
AND x1.ordr_sys_key_id < x2.ordr_sys_key_id) AS 'Line#'
FROM tablex x1
ORDER BY locn_sys_key_id, ordr_sys_key_id

Notes:
*) this method may not perform very well on a really large table
*) haven't tested this yet -- will do so ASAP

0

LVL 69

Accepted Solution

Scott Pletcher earned 100 total points
ID: 6963877
Oops, got some logic backwards.  In the WHERE, need to change "<" to ">".  Also, I didn't format the date properly.  Here is the corrected query:

SELECT ordr_sys_key_id, CONVERT(CHAR(10),ordr_dt,111) AS 'Order Date', locn_sys_key_id,
(SELECT COUNT(*) + 1
FROM tablex x2
WHERE x1.locn_sys_key_id = x2.locn_sys_key_id
AND x1.ordr_sys_key_id > x2.ordr_sys_key_id) AS 'Line#'
FROM tablex x1
ORDER BY locn_sys_key_id, ordr_sys_key_id
0

LVL 5

Author Comment

ID: 6964030
Perfect (basically), thanks.
0

Expert Comment

ID: 6964032
I´m just looking
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

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

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