CODEWITHSUNDEEP
sqlsybase
Kraken
Kraken

Reputation: 24213

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

Upvotes: 1

Views: 666

Answers (3)

Syaiful Nizam Yahya
Syaiful Nizam Yahya

Reputation: 4305

You need to use ORDER BY first, followed by ROWNUM. You will get inconsistent results if you don't follow this order.

select * from
(
   select * from TABLE T ORDER BY rowid
) where ROWNUM<=100

Upvotes: 0

Alex
Alex

Reputation: 7848

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.

Upvotes: 3

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

You need to specify an ORDER BY. Queries without explicit ORDER BY clause make no guarantee about the order in which the rows are returned. And from this result set you take the first 100 rows. As the order in which the rows can be different every time, so can be your first 100 rows.

Upvotes: 1

Related Questions