Search code examples
sqlsybase

Getting a specific number of rows from Database using RowNumber; Inconsistent results

Here is my SQL query:

select * from TABLE T where ROWNUM<=100

If i execute this and then re-execute this, I don't get the same result. Why?

Also, on a sybase system if i execute

set rowcount 100
select * from TABLE

even on re-execution i get the same result?

Can someone explain why? and provide possible solution for RowNum

Thanks

Solution

  • If you don't use ORDER BY in your query you get the results in natural order.

    Natural order is whatever is fastest for the database at the moment.

    A possible solution is to ORDER BY your primary key, if it's an INT

    SELECT TOP 100 START AT 0 * FROM TABLE
ORDER BY TABLE.ID;

    If your primary key is not a sequentially incrementing integer and you don't have another column to order by (such as a timestamp) you may need to create an extra column SORT_ORDER INT and increment in automatically on insert using either an Autoincrement column or a sequence and an insert trigger, depending on the database.

    Make sure to create an index on that column to speed up the query.