How to get sample data from a table or a view in Aster Teradata without using order by?

Question

I am trying to get sample data from a table in Aster Teradata using order by using the following code:

SELECT "col" 
FROM   (SELECT "col", 
               Row_number() 
                 OVER ( 
                   ORDER BY 1) AS RANK 
        FROM   "nisha_test"."test_table") a 
WHERE  rank <= 10000 

I want to get random 10000 rows without using order by.

Answer 1

We can use the LIMIT keyword to get random values from a table or a view in Aster DB.

select * from "nisha_test"."test_table" limit 10000;

Answer 2

You can also use the QUALIFY clause in Teradata to remove the outer SELECT:

SELECT col
FROM nisha_test.test_table
QUALIFY ROW_NUMBER() OVER (ORDER BY NULL) <= 10000

In Teradata, I think you can use a constant value in the ORDER BY. You may even be able to exclude the ORDER BY altogether: ROW_NUMBER() OVER()

Answer 3

If you want a sample you should use the built-in sample feature.

For Aster (or Vantage MLE, but with a slightly different syntax) there's a RandomSample operator, e.g.

SELECT * FROM RandomSample (
  ON (SELECT 1) PARTITION BY 1 -- dummy data, but needed
  InputTable ('nisha_test.test_table')
  NumSample ('10000')
)

For Teradata there's the SAMPLE clause, e.g.

select * 
from nisha_test.test_table 
SAMPLE 10000