Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to number rows?

2 posts in General Discussion Last posting was on 2011-06-17 18:42:15.0Z
George Brink Posted on 2011-06-17 17:19:04.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: How to number rows?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dfb8c88@forums-1-dub>
Date: 17 Jun 2011 10:19:04 -0700
X-Trace: forums-1-dub 1308331144 10.22.241.152 (17 Jun 2011 10:19:04 -0700)
X-Original-Trace: 17 Jun 2011 10:19:04 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30282
Article PK: 72464

I have a recordset:
----------------
select customer_id, action_date,
f1_int, f1_money
from table1
----------------
I would like to add into it row numbers over the field pair customer_id
and action_date. One of the data fields can be used for ordering:

For example, if I have:
cistomer_id | action_date | f1_int | f1_money
1 | 06/01/2011 | 10 | 100
1 | 06/01/2011 | 20 | 300
1 | 06/01/2011 | 50 | 150
1 | 06/03/2011 | 10 | 100
1 | 06/03/2011 | 456 | 100
2 | 01/03/2011 | 10 | 100
2 | 01/03/2011 | 16 | 12213
2 | 03/03/2011 | 998 | 102

it should become:
cistomer_id | action_date | f1_int | f1_money | num
1 | 06/01/2011 | 10 | 100 | 1
1 | 06/01/2011 | 20 | 300 | 2
1 | 06/01/2011 | 50 | 150 | 3
1 | 06/03/2011 | 10 | 100 | 1
1 | 06/03/2011 | 456 | 100 | 2
2 | 01/03/2011 | 10 | 100 | 1
2 | 01/03/2011 | 16 | 12213 | 2
2 | 03/03/2011 | 998 | 102 | 1


Adaptive Server Enterprise/15.0.3/EBF 17775 ESD#4/P/RS6000/AIX
5.3/ase1503/2768/64-bit/FBO/Thu Aug 26 07:36:52 2010


"Mark A. Parsons" <iron_horse Posted on 2011-06-17 18:42:15.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to number rows?
References: <4dfb8c88@forums-1-dub>
In-Reply-To: <4dfb8c88@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dfba007$1@forums-1-dub>
Date: 17 Jun 2011 11:42:15 -0700
X-Trace: forums-1-dub 1308336135 10.22.241.152 (17 Jun 2011 11:42:15 -0700)
X-Original-Trace: 17 Jun 2011 11:42:15 -0700, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30283
Article PK: 72461

First idea would be something like:


select *,(select count(*)
from recordset s2
where s2.customer_id = s1.customer_id
and s2.action_date = s1.action_date
and s2.f1_int <= s1.f1_int) as num
from recordset s1
order by 1,2,3
go


Of course, this assumes that for a given customer_id/action_date pair the f1_int column is unique otherwise the '<='
test will generate duplicate num values.

On 06/17/2011 13:19, George Brink wrote:
> I have a recordset:
> ----------------
> select customer_id, action_date,
> f1_int, f1_money
> from table1
> ----------------
> I would like to add into it row numbers over the field pair customer_id and action_date. One of the data fields can be
> used for ordering:
>
> For example, if I have:
> cistomer_id | action_date | f1_int | f1_money
> 1 | 06/01/2011 | 10 | 100
> 1 | 06/01/2011 | 20 | 300
> 1 | 06/01/2011 | 50 | 150
> 1 | 06/03/2011 | 10 | 100
> 1 | 06/03/2011 | 456 | 100
> 2 | 01/03/2011 | 10 | 100
> 2 | 01/03/2011 | 16 | 12213
> 2 | 03/03/2011 | 998 | 102
>
> it should become:
> cistomer_id | action_date | f1_int | f1_money | num
> 1 | 06/01/2011 | 10 | 100 | 1
> 1 | 06/01/2011 | 20 | 300 | 2
> 1 | 06/01/2011 | 50 | 150 | 3
> 1 | 06/03/2011 | 10 | 100 | 1
> 1 | 06/03/2011 | 456 | 100 | 2
> 2 | 01/03/2011 | 10 | 100 | 1
> 2 | 01/03/2011 | 16 | 12213 | 2
> 2 | 03/03/2011 | 998 | 102 | 1
>
>
> Adaptive Server Enterprise/15.0.3/EBF 17775 ESD#4/P/RS6000/AIX 5.3/ase1503/2768/64-bit/FBO/Thu Aug 26 07:36:52 2010