Top n Function

Database
Enthusiast

Top n Function

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?

Tags (1)
4 REPLIES
Enthusiast

Re: Top n Function

Hi,

In Teradata don't exists a phisical order of files, you must retrieve the records given the specific order :

SELECT TOP 10 *

FROM DatabaseName.TableName

ORDER BY ColumnName

Regards.

Teradata Employee

Re: Top n Function

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.

Enthusiast

Re: Top n Function

Hi,

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: 

Executed as Single statement.  Failed [34 : 42000] [AsterData][ASTERJDBCDSII](34) ERROR: syntax error at or near "10" () 

Elapsed time = 00:00:00.156 

STATEMENT 1: SELECT Statement failed. 

Can anyone suggest what is the correct syntax for aster? 

Thanks in advance,

Aniket

Enthusiast

Re: Top n Function

The code logic that Dixxie provided to me worked fine.

Thanks Dixxie