Hi All,

I have a table order with columns pin, orderPath, quantity. There are 5000 data in the table already. Now, I want to add an identy column, say OrderID, but with order by column pin.

Using the following query helps but not in the order as required.

ALTER TABLE order ADD OrderID numeric(6,0) identity

Here's what I got after executing the above statement:

Pin--OrderPath--Quantity--OrderID

22 kl/pod 100 1

11 xyz/pop 200 2

33 djh/dd 200 3

44 dj/po 300 4

But here's what I would like to see:

Pin--OrderPath--Quantity--OrderID

11 xyz/pop 200 1

22 kl/pod 100 2

33 djh/dd 200 3

44 dj/po 300 4

Please advice.

asked 15 May '13, 06:16

Zorama's gravatar image

Zorama
60114
accept rate: 0%

edited 15 May '13, 09:20

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662

What version and build number are you using?

You have not given us enough information. What do you mean by "not in the order as required".

Show us your table schema.

(15 May '13, 07:31) Mark Culp

The version of Sybase is 12, although not sure of the build number Executing the query "select @@version" provides me with the following information:

Adaptive Server Enterprise/12.0.0.8/P/EBF 12169 ESD3/NT (IX86)/OS 4.0/2076/32bit/OPT/Mon Dec 06 20:43:00 2004

I meant "not in the order as I actually needed"

Table Schema: create table Order( Pin numeric not null, OrderPath varchar null, Quantity int null, OrderID numeric(6,0) identity )

(15 May '13, 09:03) Zorama
Replies hidden

This forum deals with SQL Anywhere, not ASE. Therefore I would suggest to have a look at on one of Sybase's ASE newsgroups - see http://www.sybase.com/detail?id=1012843.

(15 May '13, 09:22) Volker Barth

Since the newsgroups have been shutdown, I suggest reading the last question in the FAQ and ask your question on the ASE SCN forum.

(15 May '13, 09:36) Mark Culp

@Mark: Ah, I wasn't aware of the FAQ Update:)

(15 May '13, 11:53) Volker Barth
3

To the down-voters: please STOP down-voting ASE questions! It's rude, and out of character for this forum. Thank you.

(15 May '13, 16:01) Breck Carter
Replies hidden
1

Agree, but no where near as rude as putting 'STOP' in full capitals, it suggests you think people are incapable of reading a sentence without you highlighting the important words for them, which also seems out of character for this forum (and you ?).

(16 May '13, 05:13) Daz Liquid
1

Agreed, though ASE in full capitals does seem to hurt some folks, too:)

Enough "nanny talk": Let's get back to our usual humourous and nice way of handling questions and answers...

(16 May '13, 05:28) Volker Barth
showing 3 of 8 show all flat view

If you really want to add a column that stores a particular order (instead of letting the engine order - and number - the results based on whatever criteria you need at that time), you can

  • add a column with the appropriate data type and
  • use an UPDATE statement to fill that for the existing rows, such as an self-joining UPDATE with an ROW_NUMBER() OLAP part, and
  • would possibly need a trigger to insert/update that column for new/changed rows.

If the order should be "chronologically" and if skipping values are allowed, you could also use an easier way by adding a DEFAULT AUTOINCREMENT column which will fill the values based on the row's PK order, methinks (and if not, you could adapt that for the existing rows with an UPDATE, since DEFAULT AUTOINCREMENT can still be over-written explicitly).


If, on the other hand, you just need a "numbered" result set (which I would highly recommend), you can easily do this with ROW_NUMBER(), such as the - untested -

SELECT Pin, OrderPath, Quantity,
   ROW_NUMBER() OVER (ORDER BY Pin) as OrderID
FROM MyTable
ORDER BY OrderID

Apparently, ORDER BY Pin would do, as well.

permanent link

answered 15 May '13, 08:52

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662
accept rate: 32%

edited 15 May '13, 09:01

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×50
×16
×13

question asked: 15 May '13, 06:16

question was seen: 3,123 times

last updated: 16 May '13, 05:28