Select FIRST vs Select TOP
Hint Ref: 021101040002
Hint Date: 04/01/2011
Hint Details:
Version 1 and early version 2 of Space Manager used Sybase versions below 9 and these provided a method of obtaining just the first record from a SELECT query.
E.g.: Select first CustomerNumber from Customer.
However, whilst later versions of Sybase still support this option, it is only there for compatibility and comes with restrictions. In many situations were FIRST worked previously, you will now get an error stating that the result is non-deterministic.
However, what does this mean?
Put simply, the query does not know which record to return as the query was not ordered and the first record would depend on the order in force. The solution is to add an ORDER BY to the query, but in fact the FIRST statement has been replaced with a much more versatile option called TOP.
TOP can not only return the first record of a query, but also any number of top records. It can even return a block of records from a query by stating the number of top records to get and result row at which to start.
However, remember that TOP must ALWAYS be used with an ORDER BY
E.g.: select top 5 start at 2 customernumber,* from customer order by customernumber asc
This would return the 5 records starting from (ie including) record 2, from a list ordered by the CustomerNumber.
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)