Selecting ODD numbered rows from a Table!

Database

Selecting ODD numbered rows from a Table!

People,

As the subject says... i want to know how can i select ODD (or EVEN) numbered rows from a Table!

Thanks,
Balwant
5 REPLIES
Enthusiast

Re: Selecting ODD numbered rows from a Table!

Balwant,

There is no concept of row "numbering" within Teradata, so you can't get the odd or even numbered rows.

If you want to get every other row from a particular query, you can do something like:

select *
from tablex
qualify sum(1) over (rows unbounded preceding) mod 2 = 1;

This will give you all odd-numbered rows that would have been returned from the query without the "qualify". However, every time I execute this, I could get a different set of rows (although the number of rows would stay consistent).

Why do you want to do this?

Barry
Enthusiast

Re: Selecting ODD numbered rows from a Table!

looks like you are trying to get a sample... in which case, you might want to try the sample clause

SELECT * FROM MYTABLE
SAMPLE 10;

gives you 10 randomly selected records

SELECT * FROM MYTABLE
SAMPLE .5;

will return half (50 percent) the rows

there are more syntax available in the DML manual.

Re: Selecting ODD numbered rows from a Table!

Thanks Barry and Joe!

Let me restate my question again...

suppose i create a table with 10 rows and then i only want to output EVEN or ODD numbered rows ( numbering is in accordance with the way data has been entered into the table).

Barry said.. there is no concept of row 'numbering' .. in that case can u tell.. how data is stored ?

Thanks!
Balwant
Enthusiast

Re: Selecting ODD numbered rows from a Table!

A record is first distributed to an AMP based on the rowhash of the primary index column values. And with in an AMP it's sorted logically in the order of Partition (if it has PPI) and then by the rowid (which is a combination of rowhash + uniqueness value).

I wonder why there's such a requirement ?

Re: Selecting ODD numbered rows from a Table!

Thanks Joe!

So for i haven't faced such a scenario. but i was just wondering if such a possibility exists!!!

neyways.. thanks a lot guys