Pull first ## rows

[DugzDMan]

Does anyone know the syntax for pulling the first, say 500, rows?


Thanks!

 

[Patten]

select *
from table
sample 500;

 

[DugzDMan]

Will sample always pull back the first ## rows or is it a random sample?

 

[Patten]

It will always pull back the same rows.

If you need it to sort on specific value
you 'll have to use

select *
from table
sample 500
order by 1;


A complete random set can be generated by using the random function as follows:

select *
from table
where random(1,3) < 3

but you are never sure how many rows you'll retrieve by this statement.

It should be interpreted as follows: next to every row a random number between 1 and 3 (so 1,2 or 3) will be set and only the rows with that random number less than 3 will be pulled out in the answer set. You can play with the limits and do sth lik where random (1,100)<67 and so on. But with this function you'll retrieve every time different rows.

Off course you could force it that it 'll retrieve always more than 500 rows and afterwards you add a sample on it

like

select *
from table
where random(1,3)<3
sample 500

Good luck.

 

[DugzDMan]

When I try this, I don't get the same results.

I ran this query and saved the results:
SELECT *
FROM MyDB.MyTable
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Then, I ran this query:
SELECT *
FROM MyDB.MyTable
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
SAMPLE 100

When I look at the results of the last query, I don't get the first 100 rows from the first one. I get 100 random rows. Just curious how to actually pull the first rows.

The reason I'm asking is a person on my team was trying to run a large query (700,00 + rows), but was running out of memory when returning the data. Using queryman, we just exported the answer set to a delimited file and it worked fine.

Thanks for the help!

 

[Patten]

You'll need to use the rank function:

sel *
from table
order by 1;

To get only the first 100 rows ordered on the first column

sel *
from table
qualify rank(column1 asc) <=101
order by 1;

 

[DugzDMan]

When I try that, I get error 3707: &quot;Syntax error, expected something like an 'IN' keyword between the ')' and the 'order' keyword.&quot;

Any ideas?

Thx

 

[Patten]

Can you post the exact sql because the example I posted works perfect

 

[DugzDMan]

Here's one of them:
SELECT *
FROM special_tables.cabs_cr073_billtabs
qualify rank(statistics_element asc)
order by 1

I also tried, rank(1 asc), rank(column1 asc), etc.

 

[Patten]

You should specify how many rows it will retrieve.

SELECT *
FROM special_tables.cabs_cr073_billtabs
qualify rank(statistics_element asc)<=501
order by 1

 

[tdatgod]

What version of software are you running?

Rank I believe came in in v2r4.

 

[dnoeth]

Some remarks:
There are no _first_ 500 rows without ORDER BY, because a table is unordered.

As the name implies SAMPLE returns a different result set every time.

Regardless of the Teradata version you always can use BTEQ:
.set retcancel on;
.set retlimit = 500;
sel ....;

OLAP funtions have been introduced in V2R3.
If you want exactly 500 rows, you'll have to rank according to a UNIQUE column/column combination, if it's not unique there will be ties.

V2R3: proprietary syntax
SEL...
QUALIFY RANK(col ASC) <= 500;

V2R4+: ANSI compliant syntax
SEL...
QUALIFY RANK() OVER (ORDER BY col ASC) <= 500;

V2R5: ANSI compliant syntax, always unique
SEL...
QUALIFY ROW_NUMBER() OVER (ORDER BY col ASC) <= 500;

Dieter