All Forums Database
tkbharani 2 posts Joined 11/08
07 May 2009
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.

Balamurugan B 81 posts Joined 09/07
07 May 2009

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 AInner join Table2 BOn A.Col1 = B.col1group by 1,2,3You can consider this as derive table and proceed with other required joins.Regards,Balamurugan

Regards,
Balamurugan

tkbharani 2 posts Joined 11/08
07 May 2009

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.

Balamurugan B 81 posts Joined 09/07
07 May 2009

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

Regards,
Balamurugan

Adeel Chaudhry 773 posts Joined 04/08
07 May 2009

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 , Col2FROM Table1QUALIFY 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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.