Search code examples
sqlt-sqlsequencesybasesap-ase

How to select from a table adding a sequence column?

Given a table MY_TABLE in a Sybase database, consisting of COLUMN_A, COLUMN_B, COLUMN_C, SORTER_COLUMN, I want to select as follows:

SELECT COLUMN_A, COLUMN_B, SORTER_COLUMN, SEQUENCE
FROM MY_TABLE
WHERE COLUMN_C = 'SOMETHING'
ORDER BY SORTER_COLUMN

Where SEQUENCE represents the sequence as sorted by SORTER_COLUMN -starting from 1, 2, 3, ...etc. How can I achieve that in Sybase?

I used to insert the records in a new table containing a column SEQUENCE defined as IDENTIFIER. But I need a solution to add a sequence directly into the select without having to insert into a table.

Solution

  • If you use the later versions of Sybase (at least version 12) you can either use the NUMBER(*) function:

    SELECT COLUMN_A, COLUMN_B, SORTER_COLUMN, Number(*) as SEQUENCE
FROM MY_TABLE
WHERE COLUMN_C = 'SOMETHING'
ORDER BY SORTER_COLUMN

    Or the ROW_NUMBER() function

    SELECT COLUMN_A, COLUMN_B, SORTER_COLUMN, ROW_NUMBER() OVER (ORDER BY SORTER_COLUMN) SEQUENCE
FROM MY_TABLE
WHERE COLUMN_C = 'SOMETHING'
ORDER BY SORTER_COLUMN

    UPDATE:

    As amyassin commented below both of the above functions are not working in Sybase 15.7 despite a fact that they are documented in the specification. Therefore, the only option you have in my opinion is to use a correlated query, like this:

    SELECT b.COLUMN_A, b.COLUMN_B, b.SORTER_COLUMN, 
(select count(*)+1 from MY_TABLE a where a.SORTER_COLUMN < b.SORTER_COLUMN) as SEQUENCE
FROM MY_TABLE b
WHERE b.COLUMN_C = 'SOMETHING'
ORDER BY b.SORTER_COLUMN