SQL - limit no. of rows returned.....

[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.

 

[gregsimpson]

It really depends on which RDBMS you are using.

With DB2 you can append FETCH FIRST xxx ROWS ONLY to your select statement.

With ORACLE you can append WHERE ROWNUM < xxx

Others do it differently.

 

[ADB1]

We are using Teradata.

 

[gregsimpson]

Follow this link into the teradata forum. More appropriate for your question. Looks like it has the answer for you.

thread328-253753

 

[ADB1]

Thanks Greg.

Adam.

 

[KoopLaFez]

you can also use SELECT TOP 100 as your select statemnt. I'm not sure if the TOP keword is an ANSI standard or just in T-SQL though

 

[tdatgod]

Hi,
We answered this question in the Teradata forum by suggesting SAMPLE.

However this is the ANSI SQL forum so I guess that Begs the question.

is there an ANSI way to limit the number of rows returned from a query?

I mean if there is an ANSI way to do it than every RDBMS that is ANSI SQL compliant would support that method.

 

[karluk]

Please read faq220-275 for an ANSI compliant solution.

 

[tdatgod]

Hi,
Thanks. I guess I will stick with vendor specific features for this since the performance of the FAQ example query would be pretty poor.

It would cause 'n' passes over the table where 'n' is the number of entries you want returned.

Thanks for the information.