All Forums Database
teradatauser2 236 posts Joined 04/12
05 Dec 2012
Union vs Group by




I have a question for Union vs group by.


I need to remove duplicates from financial.checking_tran using union.


sel tran_id,cust_id,acct_nbr from 



sel '0' as tran_id,'0' as cust_id, max(1) as acct_nbr from 


group by 1,2


The other option is using:

sel tran_id,cust_id,acct_nbr, count(*) as mcount from 


group by 1,2,3

where mcount<=1



From a performnce point, which is better ?




ulrich 816 posts Joined 09/09
05 Dec 2012

The standard answer is that it can depend on your data, the PI and configuration.
So easiest is to run the different options and check the DBQL.
You also forgot the set option of a temp table. Or the row_number() over...=1 posibility.
In case you have problems to interpret the DBQL numbers share the results.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ToddAWalter 316 posts Joined 10/11
05 Dec 2012

If you want to find the unique set of <tran_id,cust_id,acct_nbr> then use DISTINCT.

SELECT DISTINCT tran_id,cust_id,acct_nbr FROM financial.checking_tran;

sthatoju 1 post Joined 06/12
06 Dec 2012

GROUP BY is required if you're aggregating data, but in many cases, DISTINCT is simpler to write and read if you aren't aggregating data.

You must sign in to leave a comment.