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

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.

3 answers

  • answered 2019-11-14 05:41 Ed Bangga

    row_number() requires order by, but you can use dummy select if you don't want specific columns to be sorted based on order by.

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

  • answered 2019-11-14 06:49 ravioli

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

  • answered 2019-11-14 10:03 dnoeth

    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