Home » SQL & PL/SQL » SQL & PL/SQL » Get Distinct Data (Oracle )
Get Distinct Data [message #682488] Sun, 25 October 2020 11:06 Go to next message
james94539
Messages: 4
Registered: November 2018
Junior Member
Hi, I have the following Query

select mp.organization_code,msib.segment1 item_number,msib.description,mctl.description item_rank
from APPS.MTL_ITEM_CATEGORIES mic ,apps.mtl_categories_tl mctl,apps.mtl_system_items_b msib,apps.mtl_parameters mp
where mic.inventory_item_id=msib.inventory_item_id
and mic.category_set_id=1100000042
and mic.organization_id in (102,103,464,463)
and mic.category_id=mctl.category_id
and msib.organization_id=101
and mic.organization_id=mp.organization_id
and msib.segment1 in (404187,388925)

The result of query is below

Organization_code Item_number Description item_rank
CHI 404187 Tito's Handmade Vodka 5/12 Pack 50ml Brick Pack 80% Of Sales
CHI 388925 Tito's Handmade Vodka 5/12 Pack 50ml Shrink Wrap Do Not Reorder
MGH 404187 Tito's Handmade Vodka 5/12 Pack 50ml Brick Pack 15% Of Sales
MGH 388925 Tito's Handmade Vodka 5/12 Pack 50ml Shrink Wrap Do Not Reorder
AUB 404187 Tito's Handmade Vodka 5/12 Pack 50ml Brick Pack 80% Of Sales
AUB 388925 Tito's Handmade Vodka 5/12 Pack 50ml Shrink Wrap Do Not Reorder

From the result, we can see there are duplicate Descriptions,
Can anyone help to modify query to return unique description?

That is only return results with one column ( Description), without other 3 columns

so the result will be


Description

Tito's Handmade Vodka 5/12 Pack 50ml Shrink Wrap
Tito's Handmade Vodka 5/12 Pack 50ml Brick Pack

Thanks,
James

Re: Get Distinct Data [message #682491 is a reply to message #682488] Sun, 25 October 2020 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Re: Get Distinct Data [message #682492 is a reply to message #682488] Sun, 25 October 2020 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 07 August 2020 07:09
...And if you want to continue to get help you should envisage to feedback in your topics.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


[Updated on: Sun, 25 October 2020 11:59]

Report message to a moderator

Re: Get Distinct Data [message #682493 is a reply to message #682491] Sun, 25 October 2020 12:14 Go to previous messageGo to next message
james94539
Messages: 4
Registered: November 2018
Junior Member
SELECT mp.organization_code,
msib.segment1 item_number,
msib.description,
mctl.description item_rank
FROM apps.mtl_item_categories mic,
apps.mtl_categories_tl mctl,
apps.mtl_system_items_b msib,
apps.mtl_parameters mp
WHERE mic.inventory_item_id = msib.inventory_item_id
AND mic.category_set_id = 1100000042
AND mic.organization_id IN ( 102, 103, 464, 463 )
AND mic.category_id = mctl.category_id
AND msib.organization_id = 101
AND mic.organization_id = mp.organization_id
AND msib.segment1 IN ( 404187, 388925 )
  • Attachment: export.pdf
    (Size: 2.29KB, Downloaded 1453 times)
Re: Get Distinct Data [message #682495 is a reply to message #682493] Sun, 25 October 2020 12:51 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BlackSwan wrote on Sun, 25 October 2020 17:43
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Michel Cadot wrote on Sun, 25 October 2020 17:58
Michel Cadot wrote on Fri, 07 August 2020 07:09
...And if you want to continue to get help you should envisage to feedback in your topics.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.


Previous Topic: Split Partition with Online / Update global indexes clause
Next Topic: temp table not returning data in WITH
Goto Forum:
  


Current Time: Fri Mar 29 06:38:36 CDT 2024