Every time I select the Top 10 rows of a table I get different results. I thought the Top function return the exact order that the records were stored in the database table. I have a data issue I'm attempting to pin down but dont know which record it is and its not helpingif the result are continuously changing. Is there a better way?
In Teradata don't exists a phisical order of files, you must retrieve the records given the specific order :
SELECT TOP 10 *
ORDER BY ColumnName
As Dixxie said, you must specify an ORDER BY clause to get predictable, repeatable results with the TOP n clause.
Your rows are stored on different AMPs, and the query is processed in parallel on different AMPs. Without an ORDER BY clause, there is a race condition as to which AMPs happen to return their rows soonest, and are included in the TOP n. That's why you can get different results from one query execution to the next, when you omit the ORDER BY.
I am not sure if this holds for Aster too. When I try running the code snippet suggested by Dixxie, I get the following error:
Can anyone suggest what is the correct syntax for aster?
Thanks in advance,