Get Distinct Data [message #682488] |
Sun, 25 October 2020 11:06 |
|
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 #682492 is a reply to message #682488] |
Sun, 25 October 2020 11:58 |
|
Michel Cadot
Messages: 68665 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 |
|
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 1702 times)
|
|
|
Re: Get Distinct Data [message #682495 is a reply to message #682493] |
Sun, 25 October 2020 12:51 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
BlackSwan wrote on Sun, 25 October 2020 17:43Please 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:58Michel 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.
|
|
|