Limit no. of rows returned... 1

[ADB1]

Is there a function within sql to return a limited / snapshot number of rows when running a query. ie. the first 100 rows from a large table.

Thanks,

Adam

 

[teradataman]

There is no such thing as 'first' in the Teradata world. You can limit the result set to 100 rows by using 'SAMPLE 100' at the end of your SELECT. It's up to you what these 100 rows are based on the conditions that you specify.

 

[ADB1]

Sorry......

Could you please tell me the syntax, where exactly do you put the 'SAMPLE 100'.

Thanks.

 

[BillDHS]

Select * from table1 SAMPLE 1000;

 

[ADB1]

I have tried this, but get the following error:

Code = 3707.
Syntax error, expected something like a 'WITH' keyword between the word 'SAMPLE' and the Integer.
Output directed to Answer set window

Thanks,

Adam

 

[BillDHS]

Post the exact SQL you submitted.

 

[ADB1]

As requested:

Select * from supplier3_gql.address SAMPLE 1000;

Cheers.

 

[tdatgod]

Hi,
What version of Software are you running? I believe Sample is a V2r4 feature.

 

[ADB1]

That will be the problem then!!!!

We have v2r2!

Thanks for the help anyway, much appreciated.

Adam.

 

[BillDHS]

SAMPLE is in V2R3, I think RANDOM is V2R4

 

[tdatgod]

Hi,
if you are using BTEQ you could try

.retlimit 100

this will limit the BTEQ display to 100 rows. It will continue to return all the rows, but just not display them.

If you specify

.retcancel on

it will also cause all the rest of the output once the retlimit has been reached.

Now again I don't know if these options would be available in your version of BTEQ at v2r2.