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.

Is this really an Aster system, which release? – dnoeth Nov 14 '19 at 09:32 — dnoeth, Nov 14 '19 at 09:32
Yes, it is Aster only. Aster DB 6.2 – Nisha Tamta Nov 19 '19 at 06:56 — Nisha Tamta, Nov 19 '19 at 06:56

score 3 · Answer 1 · answered Nov 14 '19 at 10:03

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

ravioli · Answer 2 · 2019-11-14T07:11:02.593

0

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()

edited Nov 14 '19 at 07:11

answered Nov 14 '19 at 06:49

ravioli

3,749
3
14
28

score 0 · Accepted Answer · answered Nov 19 '19 at 07:01

0

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;

answered Nov 19 '19 at 07:01

Nisha Tamta

13
6

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

3 Answers3