Row number in Sybase tables

Question

Sybase db tables do not have a concept of self updating row numbers. However , for one of the modules , I require the presence of rownumber corresponding to each row in the database such that max(Column) would always tell me the number of rows in the table.

I thought I'll introduce an int column and keep updating this column to keep track of the row number. However I'm having problems in updating this column in case of deletes. What sql should I use in delete trigger to update this column?

Answer 1

You can easily assign a unique number to each row by using an identity column. The identity can be a numeric or an integer (in ASE12+).

This will almost do what you require. There are certain circumstances in which you will get a gap in the identity sequence. (These are called "identity gaps", the best discussion on them is here). Also deletes will cause gaps in the sequence as you've identified.

Why do you need to use max(col) to get the number of rows in the table, when you could just use count(*)? If you're trying to get the last row from the table, then you can do

select * from table where column = (select max(column) from table).

Regarding the delete trigger to update a manually managed column, I think this would be a potential source of deadlocks, and many performance issues. Imagine you have 1 million rows in your table, and you delete row 1, that's 999999 rows you now have to update to subtract 1 from the id.

Answer 2

I'm not sure why you would want to do this. You could experiment with using temporary tables and "select into" with an Identity column like below.

create table test
 ( 
 col1 int,
 col2 varchar(3)
 )

 insert into test values (100, "abc")
 insert into test values (111, "def")
 insert into test values (222, "ghi")
 insert into test values (300, "jkl")
 insert into test values (400, "mno")

select rank = identity(10), col1 into #t1 from Test
select * from #t1

delete from test where col2="ghi"

select rank = identity(10), col1 into #t2 from Test
select * from #t2

drop table test
drop table #t1
drop table #t2

This would give you a dynamic id (of sorts)

Answer 3

Delete trigger

CREATE TRIGGER tigger ON myTable FOR DELETE
AS 
update myTable 
set id = id - (select count(*) from deleted d where d.id < t.id)  
from myTable t

To avoid locking problems

You could add an extra table (which joins to your primary table) like this:

CREATE TABLE rowCounter 
(id    int,      -- foreign key to main table
 rownum int)

... and use the rownum field from this table.
If you put the delete trigger on this table then you would hugely reduce the potential for locking problems.

Approximate solution?

Does the table need to keep its rownumbers up to date all the time?
If not, you could have a job which runs every minute or so, which checks for gaps in the rownum, and does an update.

Question: do the rownumbers have to reflect the order in which rows were inserted?
If not, you could do far fewer updates, but only updating the most recent rows, "moving" them into gaps.

Leave a comment if you would like me to post any SQL for these ideas.

Answer 4

I want to Topper 3 for every Class in a single result with there marks like below

Class  Rank  Name  Marks
I      1     Mary  966
I      2     Gomes 964
I      3     Tom   960
II     1     Geney 967
II     2     Leena 966
II     3     Mac   964

Kindly advice any query