What is a good way to assign order #s to ordered rows in a table in Sybase

Question

I have a table T (structure below) which initially contains all-NULL values in an integer order column:

col1  varchar(30),
col2  varchar(30),
order int NULL

I also have a way to order the "colN" columns, e.g.

SELECT * FROM T ORDER BY some_expression_involving_col1_and_col2

What's the best way to assign - IN SQL - numeric order values 1-N to the order table, so that the order values match the order of rows returned by the above ORDER BY?

In other words, I would like a single query (Sybase SQL syntax so no Oracle's rowcount) which assigns order values so that SELECT * FROM T ORDER BY order returns 100% same order of rows as the query above.

The query does NOT necessarily need to update the table T in place, I'm OK with creating a copy of the table T2 if that'll make the query simpler.

NOTE1: A solution must be real query or a set of queries, not involving a loop or a cursor.

NOTE2: Assume that the data is uniquely orderable according to the order by above - no need to worry about situation when 2 rows can be assigned the same order at random.

NOTE3: I would prefer a generic solution, but if you wish a specific example of ordering expression, let's say:

SELECT * FROM T 
ORDER BY CASE WHEN col1="" THEN "AAAAAA" ELSE col1 END, ISNULL(col2, "ZZZ")

Answer 1

DVK

People have answered your specific question, and you have chosen an answer.

But the whole issue has a deeper problem.

1. From what I can understand, the Order is a column in the table; which identifies the Order in which the rows are to be presented (or similar). Is that correct ?

2. If so, then the table is massively denormalised: you have broken 3NF (Functional Dependency rule is broken); you have a derived value in a column (duplicated information); introduced Update Anomalies (when you update data in a column, you now need to update the duplicated [dependent] data in another column).

3. The most important of those is of course the Update Anomalies. You can no longer insert a row into the (now non-relational, unnormalised) table. For every insert, you have to recalculate and UPDATE the Order column for at least one and at most every other row in the table.

4. None of that is necessary.

   • I could answer your question within the scope of your question only, to populate the Order column (to be called by the code segment that constantly maintains it). The code required is 5 lines. Pure standard SQL on the base table. No views. No temp tables. No loops or cursors. Works the same as the Oracle RANK() function, and is therefore generic (just substitute your table and column names).

   • but the point is, that is the same code that could and would be used at runtime to produce the Order column, if it was a Derived Value, not a real column in the database. So again, the Order column, and all the code required to maintain it, can be removed.

   • therefore I may as well give you the code to produce the Order or Rank [derived] value directly from your base tables, without regard to the Order column.

5. Following that, the assignment of Order is trivial. Another way of stating that is, I would replace your first SELECT, and perform both functions in one code segment; there is no need for the COUNT() as a separate code segment.

Are you interested ?

In which case I need to know the exact col_N types, and what the Order value is based on, eg. highest Student Mark. Table Definition (obfuscated) is easier.

Response to Comments

1. Well, if it is a temp table, there is a much easier way, a different approach:
   • Give it an Order column
   • define it as IDENTITY
   • ensure the SELECT you use to populate it has the correct ORDER BY
     .

2. Do you still need the Order computed ?
   If so, pardon me, I don't know how to make this sound more nice. I realise you think I do not need to know the answer to my question; but the code I use is beyond that threshold, otherwise you would have coded it by now (you seem to have some experience SQL and Sybase).

   I am not "drafting", I have had the code for 20 years. In each instance, I just have to plug in the specific table and column names, takes 10 seconds. Therefore I do know what the code needs (as in, I understand the logical principle the code uses, the code itself is dead simple). And of course there are variations, because it is a logical solution, not merely a piece of clever code: one person wants Students ranked by Mark; someone else wants two columns with the second one DESCending.

   • It always takes longer to explain the problem, than to fix it.

   • Eg. you have 2 columns (the description is fine, thanks). But you are asking for an ordinal, which (by definition) requires only one column or aggregate. Imagine an Unique Clustered Index on (col_1, col2), which is what you are saying the Order needs to identify. I do not understand why is col_2 relevant. If the ordinal for the row where col_1 is "KKKKKK" is 12345, then the ordinal for the row is "KKKKKL" will have to be 12346; regardless of what col_2 is, in either of those two rows. It is the leading column of the index.

   • The code I supply has to take whatever you are logically doing in that ORDER BY clause into account. Otherwise it will be "unfinished" or "incomplete", and you still have to mess with it; which is not going to be fair to you because you do not understand the logical principle behind it.

   • Then you have the complication due to the use of VARCHARs, which is easy to code around, but I need to know what it is. Using my Clustered Index visualisation, which of these four is correct ? Do I need LTRIM() ? ( col_1 , col2)
( CONVERT( CHAR(30), col_1 ) , CONVERT( CHAR(30), col_2 ) )
( col_1 + col_2 ) -- as varchars
( CONVERT( CHAR(30), col_1 ) + CONVERT( CHAR(30), col_2 ) )

Cheers

Answer 2

Hope this solution is suitable.

Add a new Field to table T, eg, ID

Then

update T set ID = number() from T order by Col1, Col2

Not sure of the version of Sybase you are using, but this works on 5.5.

A select * from T order by col1, col2 will now give the same result as select * from T order by ID

Cheers AL

Answer 3

I used as an example the roysched table in pubs2. I added a column id (int NULL) and used as an ordering logic the following:

substring(title_id,3,4) + right(replicate('0',5) + convert(varchar,lorange),5)

Then I created (and updated) a view. To avoid duplicating, I used strings

Declare @strSql varchar(1000), @strOrder varchar(256)
set @strOrder = "substring(title_id,3,4) + right(replicate('0',5) + convert(varchar,lorange),5)"

set @strSql = "create view my_roysched (id,my_order)
as 
select id, (select count(*) from (select " + @strOrder + " as my_order from roysched) BETA 
              where BETA.my_order <= ALPHA.my_order) as my_id 
      from (select id," + @strOrder + " as my_order from roysched ) as ALPHA 

update my_roysched 
set id = my_order

drop view my_roysched"

exec (@strSql)

Now if you give

select * from roysched
order by substring(title_id,3,4) + right(replicate('0',5) + convert(varchar,lorange),5)

you will see that id match the order of rows returned by the above order by