Select query in teradata

prasanthiv · New User Joined: 07 Dec 2008 Posts: 6 Location: INDIA

Hi,

I have a data like shown below and is coming from my select query in teradata.

dick scherrer · Posted: Wed Feb 24, 2010 10:26 pm

Hello,

Neither "what you have" nor "what you want is clear from your post.

Why is distinct mentioned?

Suggest you provide a more detailed explanation of the requirement.

Keep in mind that your question is completely clear to you, but may not be to others, so more clarification is needed.

prasanthiv · New User Joined: 07 Dec 2008 Posts: 6 Location: INDIA

Data in table A

COLID | COLB| COLC| COLD
11 | 22 | AB | BC
11 | 23 | CD | DE

Table B

same structure as table A

As shown above

We can see more than one row in table A for COLID "11".

When am inserting data in to table B from table A, I need one entry of COLID "11" inserted into table B

So after inserting data in table B, data in table B should Be either

TABLE B
COLID | COLB| COLC| COLD
11 | 22 | AB | BC

or

TABLE B
COLID | COLB| COLC| COLD
11 | 23 | CD | DE

Hope am clear this time..Sorry if am not clear still..

Akatsukami · Posted: Thu Feb 25, 2010 12:19 am

So you're looking for something like

prasanthiv · New User Joined: 07 Dec 2008 Posts: 6 Location: INDIA

Yes..am looking some thing similar but fetch first row only won't work in teradata sql.. It works in DB2

Thanks,
Prasanthi.

prasanthiv · New User Joined: 07 Dec 2008 Posts: 6 Location: INDIA

HI All,

I tried something like this and it is working. Since i can select any of one entry for COLID 11

Insert into table B
Select COLID
,max(COLB)
,max(COLC)
,max(COLD)
from table A
Group by 1;

If anybody has better solution please let me know..Thank you all for your time...

Prasanthi