generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.

Generate Sequential Numbers - T-SQL

Hi
i have a table with a field called response id. i would like to update this
field with sequential numbers starting from 1000.
can someone please advise me how i go about this.
any help would be appreciated.
thanks heaps in advance
Kind Regards
RidhimaTested on NORTHWIND:
alter table ORDERS
add RESPONSEID int
GO
update ORDERS
set RESPONSEID = RowNumber + 999
from ORDERS inner join
(select ROW_NUMBER() OVER (order by OrderID) as RowNumber, ORDERID from
ORDERS) as TMP on
TMP.ORDERID = ORDERS.ORDERID
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Ridhima" <Ridhima@.discussions.microsoft.com> wrote in message
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima|||On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
> Hi
> i have a table with a field called response id. i would like to update this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima
Another way to control this moving forward is to select Design for the
table in SSMS and select the ResponseID field. In the Column
Properties section, select Identity Specification >> (Is Identity) and
set it to Yes. Set Identity Increment to 1 and then set Identity Seed
to 1000 (which is the starting integer value of the ResponseID field).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That was my first thought too. But when I tested it, it created "random"
values in the ResponseID field.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:29bb8aeb-5839-42b5-8e27-57978af3f160@.v4g2000hsf.googlegroups.com...
> On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
>> Hi
>> i have a table with a field called response id. i would like to update
>> this
>> field with sequential numbers starting from 1000.
>> can someone please advise me how i go about this.
>> any help would be appreciated.
>> thanks heaps in advance
>> Kind Regards
>> Ridhima
>
> Another way to control this moving forward is to select Design for the
> table in SSMS and select the ResponseID field. In the Column
> Properties section, select Identity Specification >> (Is Identity) and
> set it to Yes. Set Identity Increment to 1 and then set Identity Seed
> to 1000 (which is the starting integer value of the ResponseID field).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||declare @.secuence int
set @.secuence = 999
update table
set @.secuence = response_id = @.secuence + 1
PS.
It does work ! Have faith !
"Ridhima" <Ridhima@.discussions.microsoft.com> escribió en el mensaje
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima

generate a sequential number

Hi,
I'm trying to generate a sequential number as part of a query I have.
I'm sorting the query using one of the items and need to generate a sequential number from 1 to N for the number of items in the selection.
I thought it sounds easy but could not figure this outHelp please

Thanks
MottyHi, I'm trying to generate a sequential number as part of a query I have. I'm sorting the query using one of the items and need to generate a sequential number from 1 to N for the number of items in the selection. I thought it sounds easy but could not figure this out
Q1 Help please
Thanks Motty

A1 As presented, this is a very general requirement however one approach to the issue (which may or may not be suitable depending on the detailed requirements), may be to insert the result set into a newly created #Temp table with an identity column seeded at 1.|||Same functionality with identity function and select into

select OrderId=identity(int,1,1),*
into #Table
from YourTable
order by ColumnX