UDA

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-24-2006
02:29 AM

11-24-2006
02:29 AM

How to select only a particular row from a table which i am interested based on position. not a set of rows but only that row which i am interested.

in oracle we can do like this

SELECT * FROM EMP WHERE(ROWID, 0) IN (SELECT ROWID, MOD (ROWNUM, 4) FROM EMP);

How to achieve the same in TERADATA

in oracle we can do like this

SELECT * FROM EMP WHERE(ROWID, 0) IN (SELECT ROWID, MOD (ROWNUM, 4) FROM EMP);

How to achieve the same in TERADATA

7 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-27-2006
04:50 AM

11-27-2006
04:50 AM

I was wondering if we can play around using HashRow() function to acheive this. Not very sure though!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-27-2006
11:06 AM

11-27-2006
11:06 AM

What are you trying to do that requires you to get to the specific ROWID?

It looks like the particular SQL that you submitted is trying to get a sample of 25% of the rows. You can do that with Teradata by doing this:

select *

from table

sample .25;

It looks like the particular SQL that you submitted is trying to get a sample of 25% of the rows. You can do that with Teradata by doing this:

select *

from table

sample .25;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-30-2006
12:58 AM

11-30-2006
12:58 AM

Hi Barry,

I have a question regarding SAMPLE. SInce this thread has some discussion regarding the SAMPLE i will put my doubts here.

How does sample select the rows, does it select rows randomly or in the order of hash or rowid? .Suppose, if we specify sample .25, does it select the first 25% rows or does it pick up rows randomly?

regarding this threads question, looks to me that we are interested in the first few rows or some particular row based on Row ID. How does using sample help in this case. Please let me know if Iam missing something here

I have a question regarding SAMPLE. SInce this thread has some discussion regarding the SAMPLE i will put my doubts here.

How does sample select the rows, does it select rows randomly or in the order of hash or rowid? .Suppose, if we specify sample .25, does it select the first 25% rows or does it pick up rows randomly?

regarding this threads question, looks to me that we are interested in the first few rows or some particular row based on Row ID. How does using sample help in this case. Please let me know if Iam missing something here

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-04-2006
08:04 AM

12-04-2006
08:04 AM

Leo,

My understanding is that it picks the rows randomly (i.e. not by hash ID or ROWID). However, you can decide whether you want it to take a random sample by AMP (i.e. approximately the same number of rows from each AMP) or across AMPs. The default is for it to take the same number of rows (or approximately) for each AMP. This is also faster. However, this would not be a "true" random sample of the table since rows that are on AMPs with fewer rows would have a higher percentage chance of being selected than rows that are on AMPs with more rows. However, the default method would probably work for most applications. If you need to get a "true" sample, then you need to specify "RANDOMIZED ALLOCATION".

The reason that I brought up the "sample" in the first place was that it appeared that in the query, dixon was trying to get 1/4 of the rows by doing a "mod 4" on "ROWNUM" and then selecting that rows that had a remainder of 0. This would get you about 1/4 of the rows, assuming that "ROWNUM" is sequential. So, I was just suggesting another method of getting 1/4 of the rows if that's what he was trying to do.

My understanding is that it picks the rows randomly (i.e. not by hash ID or ROWID). However, you can decide whether you want it to take a random sample by AMP (i.e. approximately the same number of rows from each AMP) or across AMPs. The default is for it to take the same number of rows (or approximately) for each AMP. This is also faster. However, this would not be a "true" random sample of the table since rows that are on AMPs with fewer rows would have a higher percentage chance of being selected than rows that are on AMPs with more rows. However, the default method would probably work for most applications. If you need to get a "true" sample, then you need to specify "RANDOMIZED ALLOCATION".

The reason that I brought up the "sample" in the first place was that it appeared that in the query, dixon was trying to get 1/4 of the rows by doing a "mod 4" on "ROWNUM" and then selecting that rows that had a remainder of 0. This would get you about 1/4 of the rows, assuming that "ROWNUM" is sequential. So, I was just suggesting another method of getting 1/4 of the rows if that's what he was trying to do.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-07-2006
12:51 AM

12-07-2006
12:51 AM

Thanks for your explanation Barry.The information on SAMPLing was very useful.Also, I got your point regarding dixon's question.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-25-2007
10:18 AM

01-25-2007
10:18 AM

This thread has some useful information..Thanks for the posts.

Anyway,the question remains unanswered here.

Any thoughts how to retrieve a SAMPLE ROW in Teradata.I understand that SAMPLE n will give the RANDOM n% from the AMPs (evenly or randomly depending on the initial allcation).

But how can I target the EXACT ROW that I am interested in?..

Anyway,the question remains unanswered here.

Any thoughts how to retrieve a SAMPLE ROW in Teradata.I understand that SAMPLE n will give the RANDOM n% from the AMPs (evenly or randomly depending on the initial allcation).

But how can I target the EXACT ROW that I am interested in?..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-25-2007
10:41 AM

01-25-2007
10:41 AM

Hi suman,

One possibility would be to use the Ranking function in conjunction with a qualify statement for the number. Just Rank the various rows on your key and order it by the key sequence. Then use the Qualify rank = x function to pull the row number. There is no direct equivalent of the ROWID function in Oracle here but this is an effective work around. But as you can see, if the table is large, the partitioned window function causes quite a burden on the system resources. So use the function judicially and also ensure that the ranking keys and ordering key sequence are smartly chosen so that you always get the rank as a unique number rather than having multiple rows with the same rank. I would be glad to help with any further details if you need.

One possibility would be to use the Ranking function in conjunction with a qualify statement for the number. Just Rank the various rows on your key and order it by the key sequence. Then use the Qualify rank = x function to pull the row number. There is no direct equivalent of the ROWID function in Oracle here but this is an effective work around. But as you can see, if the table is large, the partitioned window function causes quite a burden on the system resources. So use the function judicially and also ensure that the ranking keys and ordering key sequence are smartly chosen so that you always get the rank as a unique number rather than having multiple rows with the same rank. I would be glad to help with any further details if you need.