distinct rows

Database

distinct rows

Hi

I have a query which outputs more than one row for each ID. I need to select the first row of the result only for each ID and process for next joins. And also will group by outputs the first row of the result set only.
4 REPLIES
Enthusiast

Re: distinct rows

Hi bharani,

'Group BY' can be used after a join condition to restrict duplicate records in results sets.

Select A.Col1,A.col2, B.Col3 from Table1 A
Inner join Table2 B
On A.Col1 = B.col1
group by 1,2,3

You can consider this as derive table and proceed with other required joins.

Regards,
Balamurugan

Re: distinct rows

yes. But I have more than 30 columns , then I have to add group by for all columns.
So is there any other way other than group by.
Enthusiast

Re: distinct rows

Hi,

Try filtering the records before doing the join operation, so that in Join you won't get duplicate records.

If it is not possible then you have to use Group By/Distinct to avoid duplicates.

Regards,
Balamurugan
Teradata Employee

Re: distinct rows

Hello,

GROUP BY clause does not return the 'first row', instead it groups the values of the columns it is defined on!

It is a good idea to provide the SQL as well, so you get the solution quickly and accurately as well.

Now, I believe ID is the column, so you need to do something similar to get the 1st row depending on the ID column:

CREATE VOLATILE TABLE Table1(Col1 INTEGER, Col2 INTEGER) ON COMMIT PRESERVE ROWS;

INSERT Table1 VALUES (7, 1);
INSERT Table1 VALUES (2, 2);
INSERT Table1 VALUES (5, 3);
INSERT Table1 VALUES (1, 4);

SELECT
Col1
, Col2
FROM
Table1
QUALIFY RANK(Col1 ASC) = 1;

And as you may notice, some functions/functionality cannot be used in sub-queries, you may have to put data into some table before proceeding with the join and other processing you may have.

HTH!

Regards,

Adeel