Avoid Distinct [message #661902] |
Wed, 05 April 2017 14:50 |
|
manikandan23
Messages: 34 Registered: February 2017
|
Member |
|
|
Hi,
I have few columns retrieved from the 3 tables. The tables containing records such as 1, 7 and 14 records. i.e. table 1 has 1 record, table2 has 7 records and table 3 has 14 records.
When I join these tables using a key column, I get around 98 rows (1 X 7 X 14) and all my selected columns do not have distinct values and hence all 98 rows are duplicate records.
I did not want to use DISTINCT to filter all the duplicate records and I want only one record to be produced in my output.
select WT.ID AS WT,
WT.WORK_TICKET_NUMBER AS WT_NUMBER,
WT.CRT_DATE AS WORK_TICKET_CREATE_DATE,
WTt.DISPOSITION,
re.QTY AS QTY,
FROM DW.ODS_F_WORK_TICKET_TRANS WTT --7
JOIN DW.ODS_F_WORK_TICKET WT ON WT.ID = WTT.WT_ID --1
JOIN DW.ODS_D_RULE_EXECUTION RE ON RE.WORK_TICKET_ID = WT.ID --14
WHERE wt.id = 123468517
|
|
|
|
|
|
|
Re: Avoid Distinct [message #661908 is a reply to message #661902] |
Wed, 05 April 2017 19:52 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Why don't you want to use distinct? You could also group by all of the columns in your query to produce the same result, as shown below.
-- test data:
SCOTT@orcl_12.1.0.2.0> select deptno, job from emp order by deptno, job;
DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 ANALYST
20 CLERK
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
30 SALESMAN
30 SALESMAN
30 SALESMAN
14 rows selected.
-- distinct:
SCOTT@orcl_12.1.0.2.0> select distinct deptno, job from emp order by deptno, job;
DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
9 rows selected.
-- group by :
SCOTT@orcl_12.1.0.2.0> select deptno, job from emp group by deptno, job order by deptno, job;
DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
9 rows selected.
|
|
|
Re: Avoid Distinct [message #661927 is a reply to message #661908] |
Thu, 06 April 2017 03:33 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
There are generally two ways to get rid of duplicates:
1) use distinct (or group by but you should use distinct)
2) modify the where clause to eliminate the duplicates.
2 is always preferable if it can be done (and if it can't that generally means there's something wrong with your data model).
We can't tell you how to do 2 in your case unless you give us the table structures/relationships and sample data.
|
|
|