Select First N rows

[DugsDMan]

Does anyone know the Teradata SQL statement (if there is one) that will only return the first N rows retrieved by a query?

I know some systems will allow you to:

Select First 100 Name, Address
From mytable
Where Age > 21

I have tried this on Teradata, but get an error.

Thanks,
Doug

 

[BillDHS]

You can use Sample or Random (if V2R4) to select a limited number of rows. If you want the first of an ordered set, you will need to use Rank and a Qualify clause.

 

[DugsDMan]

Thanks, but is there a way to only retrieve say 100 rows from the DB? I looked up sample and random sample and they say that all rows will be returned to the PC before the random sample is displayed.

 

[BillDHS]

I don't think RANDOM function or SAMPLE clause returns all rows, however Queryman's Random Sample does. If you are using BTEQ, you can limit the number of rows with RetLimit.

 

[DugsDMan]

I am using Queryman. What I was asked to try to figure out was how to pull a small sample of data from a database, without knowing anything about it. They didn't know what is in the fields, so nothing to use in the where clause. The table has more than 500M rows in it, so we would blow spool space everytime. We ended up just pulling a few columns, one at a time to get an idea as to what was in it and be able to use that as a constraint. Just figured there had to be a better way, something like pulling a few hundred rows back, not pull all rows back and only display a few rows. Any ideas?

Thanks!

 

[BillDHS]

Stay with the primary index to limit spool,
maybe sample on that field alone, then return those values to get the rest of the rows.

ie:
select *
from 500mtable
where PIvalues IN
(select PIvalues from 500mtable sample 500);

 

[DugsDMan]

Actually, using just:

Select *
From MyTable Sample 500

worked fine.

THANKS!!!

 

[teradataman]

"SAMPLE XXX" (rows) or "SAMPLE 0.1" (= 10%) after the normal SELECT statement is the way to go. Using a RETLIMIT value in BTEQ simply truncates the result set, and it can't be used in Queryman anyway.

To see the effect of SAMPLE, or any other technique, on the parser simply run the same SQL with the EXPLAIN modifier at the front. This returns the parsers own report on how it will execute the query. Try an EXPLAIN with and without the SAMPLE clause and note the difference.

FYI, many sites insist on the use of EXPLAIN to verify the execution plan before code goes into production.