1

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

Andrii Omelchenko
  • 13,183
  • 12
  • 43
  • 79
Kraken
  • 23,393
  • 37
  • 102
  • 162
  • 1
    to be honest, the 1.st statement is invalid (missing what is to be selected), so it won't run at all, but that's probably a minor thing here only – Peter Butkovic Aug 24 '12 at 05:16

3 Answers3

3

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.

Alex
  • 7,728
  • 3
  • 35
  • 62
  • now in this query, will it first Order by the entire set and then get me the first 100 from them? Wont it be inefficient? What if next time i want rownum 101-200? How do i do that. – Kraken Aug 24 '12 at 05:20
  • waht if my data set is huge, with tens of millions of entries. I cant wait for ORDER BY to do it for me, no other alternative? – Kraken Aug 24 '12 at 05:24
  • It won't actually order by anything. The order of the primary key is stored in an index. It will just look up the first 100 rows in the index. – Alex Aug 24 '12 at 05:25
  • I have updated my answer based on the syntax of Sybase I found here: http://stackoverflow.com/a/16753/175157 - just use `START AT 101`. – Alex Aug 24 '12 at 05:34
  • yeah, sybase is ok, what about the db2? What if my primaryindex is not INT? – Kraken Aug 24 '12 at 05:35
  • also, say i want the result set to be ORDERED BY on another COLUMN. Will it be any bad performance wise, that i do `order by REQUIRED FIELD`? – Kraken Aug 24 '12 at 05:38
  • Then 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. – Alex Aug 24 '12 at 05:39
  • IIRC, ROWNUM is for Oracle and TOP is for MSSQL. Please dont mix and match answers. It doesnt help and only confuses users. – Syaiful Nizam Yahya Jul 20 '18 at 09:20
  • @SyaifulNizamYahya what do you mean? This question is neither about Oracle, nor about MSSQL. – Alex Jul 21 '18 at 05:30
  • https://www.w3schools.com/sql/sql_top.asp. "Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM." The question is platform specific. You cannot execute the same command in the question in a platform other than Oracle. – Syaiful Nizam Yahya Jul 23 '18 at 02:14
  • @SyaifulNizamYahya We're quoting w3schools now, eh? Sorry to burst your bubble but this question is tagged Sybase and Sybase uses the TOP syntax. And another thing - this answer demonstrates a _concept_, the concept of taking the first N rows after skipping the first M rows. While databases use different _syntax_ for the same concept (TOP-START AT, FIRST-SKIP, LIMIT-OFFSET and so on), transferring the concept to the syntax of your database is a trivial search for "the syntax you see" + "the database you use". – Alex Jul 24 '18 at 04:06
  • If you find that confusing I suggest you consider moving to a simpler career path, such as selling vegetables on the market or driving a taxi. But if you choose the latter always use a GPS navigation, there are too many roads to choose from. – Alex Jul 24 '18 at 04:08
1

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.

Daniel Hilgarth
  • 171,043
  • 40
  • 335
  • 443
0

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
Syaiful Nizam Yahya
  • 4,196
  • 11
  • 51
  • 71