ROWNUM equivalent in Teradta

Question

is there anything equivalent to "ROWNUM" in teradata ? i have to implement the below query in teradata, it runs fine with oracle. any ideas or suggestions ?

    INSERT INTO ADDRES(CITY,STATEPROVINCEID) SELECT 'sample',AA.ID FROM
    AA WHERE ROWNUM<=1000

Solution 1

As there's no ORDER BY you can simply use:

INSERT INTO ADDRES(CITY,STATEPROVINCEID) 
SELECT TOP 1000 'sample',AA.ID 
FROM AA

But this is not random, it's just the first 1000 rows found on an AMP.

To get get sampled rows:

INSERT INTO ADDRES(CITY,STATEPROVINCEID) 
SELECT 'sample',AA.ID 
FROM AA
SAMPLE 1000

If you are a statistician and need a true random sample switch to:

SAMPLE RANDOMIZED ALLOCATION 1000

You can also get multiple samples, up to 16, e.g.

SAMPLE 1000,2000 --use column SAMPLEID to know which row belongs to which sample 

or a fractional sample:

SAMPLE 0.1 -- 10% of the rows

or a stratified sample, i.e. samples from different groups:

SAMPLE WHEN col< 0 THEN 10
       WHEN col <100 THEN 20 
       ELSE 50 
END

Solution 2

I'm not sure it won't help in your situation, but for future reference, Teradata has a ROW_NUMBER() function. It works pretty much like everyone else's :

ROW_NUMBER over ([PARTITION by <column>] ORDER by <column1>[<column2]...]).

Teradata has the added advantage of being able to constrain on it using QUALIFY, instead of having to use a derived table.

Select
...
from
...
QUALIFY ROW_NUMBER over (order by...)

Comments

[lazy]

'sample' is just a string which i'm inserting, i'm taking the values of id in AA, the no of rows i'm inserting may vary. when i tried your example code, it runs but rows not getting inserted. 0 Row(s) Inserted Query 1 of 1 elapsed time (seconds) - Total: 0.107, SQL query: 0.107, Building output: 0

[dnoeth]

That's strange, what client do you use? When you run only the select and it doesn't return any rows the table should be empty :-)

[lazy]

i'm using squirrel. AA table contain only ID as column with just 10 values, i'm creating ADDRES table as i'm using AA.ID values to insert 1000 records

[dnoeth]

Your source table has only ten rows in it and you want to select the TOP 1000 from it? Strange, but the problem is probably the definition of the target table, do a SHOW TABLE target; and it's a CREATE SET TABLE, i.e. silently removing duplicate rows during an insert/select. Change it to MULTISET and then use SAMPLE WITH REPLACEMENT 1000 to insert 1000 rows with a single statement.

[lazy]

its MultiSet table with two columns ADDRES(CITY,STATEPROVINCEID). in oracle it automatically populates duplicate values, which is why i need it.

[dnoeth]

If it's a MULTISET table it will accept duplicate rows. If it's still not working you need to add more details.