Home » SQL & PL/SQL » SQL & PL/SQL » Avoid Distinct (Oracle 11g)
Avoid Distinct [message #661902] Wed, 05 April 2017 14:50 Go to next message
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 #661903 is a reply to message #661902] Wed, 05 April 2017 15:05 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I cannot picture it in my mind until you post the create table and insert statements.
Re: Avoid Distinct [message #661904 is a reply to message #661902] Wed, 05 April 2017 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

BTW - Neither table nor result set have any records; only rows.

What constitutes "duplicate" record?

How do we know or decide which row is the only row you desire?
Does every column value be identical in every row?
Re: Avoid Distinct [message #661905 is a reply to message #661904] Wed, 05 April 2017 15:29 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
The columns that I have selected from these tables contain duplicates and When I pull them, I should pull only one record but I dont want to use DISTINCT.

Any row should be fine.

it would be something like this

123468517 45974660 3/15/2017 6:26:45 AM RTC 1
123468517 45974660 3/15/2017 6:26:45 AM RTC 1
123468517 45974660 3/15/2017 6:26:45 AM RTC 1
123468517 45974660 3/15/2017 6:26:45 AM RTC 1
123468517 45974660 3/15/2017 6:26:45 AM RTC 1
123468517 45974660 3/15/2017 6:26:45 AM RTC 1
123468517 45974660 3/15/2017 6:26:45 AM RTC 1
Re: Avoid Distinct [message #661906 is a reply to message #661905] Wed, 05 April 2017 15:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Realize
We don't have your tables.
We don't have your data

So we can't run, test, debug or improve posted SQL.

So add an additional where clause that limits the result set to a single row.


BTW - Did you actually take the time to read the Posting Guidelines that I provided the URL to them?
Re: Avoid Distinct [message #661908 is a reply to message #661902] Wed, 05 April 2017 19:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous message
cookiemonster
Messages: 13917
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.
Previous Topic: package
Next Topic: Pre Insert logic in Trigger
Goto Forum:
  


Current Time: Thu Mar 28 06:53:50 CDT 2024