Home » SQL & PL/SQL » SQL & PL/SQL » Problem with selecting distinct values
Problem with selecting distinct values [message #289939] Wed, 26 December 2007 15:34 Go to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
Running the following query I get duplicate upc's since some records in the product table have the same brandid, description and upc but different familyid's. Is there a way to form the query to return each upc only once regardless of whether there are rows in the product table with the same upc but different familyid? I've tried several variations of subqueries but haven't had any luck yet. Also upc is a clob.
select
p.id,
pn.productname,
p.upc,
p.productsize,
pf.formatname,
p.weight,
p.description,
pt.typename
from
product p,
family f,
productname pn,
productformats pf,
producttypes pt
where
p.productnameid = pn.id (+)
and p.formatid = pf.id (+)
and p.familyid = f.id (+)
and p.typeid = pt.id (+)
and p.brandid = 1
Re: Problem with selecting distinct values [message #289948 is a reply to message #289939] Wed, 26 December 2007 17:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Have you tried using the DISTINCT keyword? (select DISTINCT ...)

Also, it looks like you can eliminate the family table from the select statement, since you are not selecting any columns from it.

If this does not solve your problem, then please review the forum guide at the top of the forum page and post a more detailed question, including create table and insert statements for sample data that will show the problem more clearly.
Re: Problem with selecting distinct values [message #289994 is a reply to message #289939] Thu, 27 December 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

please review the forum guide at the top of the forum page and post a more detailed question, including create table and insert statements for sample data that will show the problem more clearly.

As well as Oracle version and format your post, use code tags and "Preview" button to check.

Regards
Michel
Re: Problem with selecting distinct values [message #290116 is a reply to message #289948] Thu, 27 December 2007 09:31 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
Yes, I've tried distinct. The family table was included because it's an option to pass a familyid in the query. Here's the query with it removed for clarity:
select    
                          p.id, 
                          pn.productname, 
                          p.upc, 
                          p.productsize, 
                          pf.formatname, 
                          p.weight,  
                          p.description,
			  pt.typename
                from      
                          product p, 
                          productname pn, 
                          productformats pf, 
			  producttypes pt
                where 
                          p.productnameid = pn.id (+)
                and       p.formatid = pf.id (+)
                and       p.typeid = pt.id (+)
                and       p.brandid = 1


Here are the create statements for the tables involved:
CREATE TABLE PRODUCTFORMATS
(
  ID     NUMBER,
  LABEL  VARCHAR2(100 BYTE)
);

CREATE TABLE PRODUCTNAME
(
  ID           INTEGER                          NOT NULL,
  PRODUCTNAME  VARCHAR2(150 BYTE)               NOT NULL,
  PRIMARY KEY
 (ID)
);

CREATE TABLE PRODUCTTYPES
(
  ID     NUMBER,
  LABEL  VARCHAR2(100 BYTE)
);

CREATE TABLE PRODUCT
(
  ID                          INTEGER           NOT NULL,
  PRODUCTNAMEID               INTEGER           NOT NULL,
  COMPANYID                   INTEGER           NOT NULL,
  BRANDID                     INTEGER           NOT NULL,
  FAMILYID                    INTEGER           NOT NULL,
  UPC                         CLOB,
  DESCRIPTION                 CLOB,
  WEIGHT                      VARCHAR2(100 BYTE),
  FORMATID                    NUMBER,
  TYPEID                      NUMBER,
  PRODUCTSIZE                 VARCHAR2(100 BYTE),
  PRIMARY KEY
 (ID)
);


Here are a few inserts:
insert into productformats(id,formatname) values (1,'format A');
insert into productformats(id,formatname) values (2,'format B');

insert into productname(id,productname) values (1,'name A');
insert into productname(id,productname) values (2,'name B');

insert into producttypes(id,label) values (1,'type A');
insert into producttypes(id,label) values (2,'type B');

insert into product 
(ID,PRODUCTNAMEID,COMPANYID,BRANDID,FAMILYID,UPC,DESCRIPTION,WEIGHT,FORMATID,TYPEID,PRODUCTSIZE) 
values (1,1,200,1,29,'000001','description A','10 oz',1,1,'big');
insert into product 
(ID,PRODUCTNAMEID,COMPANYID,BRANDID,FAMILYID,UPC,DESCRIPTION,WEIGHT,FORMATID,TYPEID,PRODUCTSIZE) 
values (2,1,200,1,30,'000001','description A','10 oz',1,1,'big');


I am not able to change the table structures in any way.

[Updated on: Thu, 27 December 2007 09:36] by Moderator

Report message to a moderator

Re: Problem with selecting distinct values [message #290117 is a reply to message #290116] Thu, 27 December 2007 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no duplicate in the output you get.
If you don't need some data (for instance p.id) then remove it from the select clause and distinct will work.

Regards
Michel
Re: Problem with selecting distinct values [message #290118 is a reply to message #289994] Thu, 27 December 2007 09:40 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
The full version information is Oracle Database 10g Express Edition Release 10.2.0.1.0. I've added the sql as 'code' in my replies. Thanks for the pointers.
Re: Problem with selecting distinct values [message #290119 is a reply to message #290117] Thu, 27 December 2007 09:42 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
All the fields called are used in the output.
Re: Problem with selecting distinct values [message #290120 is a reply to message #290119] Thu, 27 December 2007 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So there is no duplicate.

By the way the query you posted does not work:
SQL> select    
  2                            p.id, 
  3                            pn.productname, 
  4                            p.upc, 
  5                            p.productsize, 
  6                            pf.formatname, 
  7                            p.weight,  
  8                            p.description,
  9       pt.typename
 10                  from      
 11                            product p, 
 12                            productname pn, 
 13                            productformats pf, 
 14       producttypes pt
 15                  where 
 16                            p.productnameid = pn.id (+)
 17                  and       p.formatid = pf.id (+)
 18                  and       p.typeid = pt.id (+)
 19                  and       p.brandid = 1
 20  /
     pt.typename
     *
ERROR at line 9:
ORA-00904: "PT"."TYPENAME": invalid identifier


Regards
Michel

[Updated on: Thu, 27 December 2007 09:52]

Report message to a moderator

Re: Problem with selecting distinct values [message #290123 is a reply to message #290120] Thu, 27 December 2007 10:15 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
The inserts for the productformat table should have been:
insert into productformats(id,label) values (1,'format A');
insert into productformats(id,label) values (2,'format B');


Here is the query fully tested:
select    
                          p.id, 
                          pn.productname, 
                          p.upc, 
                          p.productsize, 
                          pf.label, 
                          p.weight,  
                          p.description,
			  pt.label, p.familyid
                from      
                          product p, 
                          productname pn, 
                          productformats pf, 
			  producttypes pt
                where 
                          p.productnameid = pn.id (+)
                and       p.formatid = pf.id (+)
                and       p.typeid = pt.id (+)
                and       p.brandid = 1


and here is the duplicate result set:
ID	PRODUCTNAME	UPC	PRODUCTSIZE	LABEL	WEIGHT	DESCRIPTION	LABEL	FAMILYID
1	name A	000001	big	format A	10 oz	description A	type A	29
2	name A	000001	big	format A	10 oz	description A	type A	30


My goal is to only display one row since both UPC's are the same. I've tried:
select    
                          distinct to_char(p.upc),p.id, 
                          pn.productname, 
                          p.productsize, 
                          pf.label, 
                          p.weight,  
                          to_char(p.description),
			  pt.label, p.familyid
                from      
                          product p, 
                          productname pn, 
                          productformats pf, 
			  producttypes pt
                where 
                          p.productnameid = pn.id (+)
                and       p.formatid = pf.id (+)
                and       p.typeid = pt.id (+)
                and       p.brandid = 1


But both rows are still returned since familyid is distinct. Familyid is required in the output.
			
TO_CHAR(P.UPC)	ID	PRODUCTNAME	PRODUCTSIZE	LABEL	WEIGHT	TO_CHAR(P.DESCRIPTION)	LABEL	FAMILYID
000001	2	name A	big	format A	10 oz	description A	type A	30
000001	1	name A	big	format A	10 oz	description A	type A	29


HTH
Re: Problem with selecting distinct values [message #290128 is a reply to message #290123] Thu, 27 December 2007 10:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi,

Distinct applies to entire resultset not for a particular column. Since distinct is not catering for your purpose you have choose which row you are interested and you have to work around it by using some of the analytical functions like dense_rank, row_number etc.

HTH

Regards

Raj

[Updated on: Thu, 27 December 2007 10:38]

Report message to a moderator

Re: Problem with selecting distinct values [message #290131 is a reply to message #290123] Thu, 27 December 2007 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So show the output you want and align your columns.

Regards
Michel
Re: Problem with selecting distinct values [message #290134 is a reply to message #290131] Thu, 27 December 2007 10:47 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
The output I need is one of the two rows (either is fine). I haven't found a way to insert a table so here is it vertically:

ID: 1
PRODUCTNAME: name A
UPC: 000001
PRODUCTSIZE: big
LABEL: format A
WEIGHT: 10 oz
DESCRIPTION: description A
LABEL: type A
FAMILYID: 29
Re: Problem with selecting distinct values [message #290136 is a reply to message #289939] Thu, 27 December 2007 10:50 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
A table is like a basket of colored balls.
Which red ball in the basket is the 2nd red ball & which is the 1st red ball?
Re: Problem with selecting distinct values [message #290138 is a reply to message #290128] Thu, 27 December 2007 10:54 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
Both of those functions look like they apply to partitions which I haven't worked with yet. I'll read up on them. thank you
Re: Problem with selecting distinct values [message #290139 is a reply to message #290136] Thu, 27 December 2007 10:55 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
Product id 1 would be the first one and product id 2 would be the second. Both are red but belong to a different family.
Re: Problem with selecting distinct values [message #290140 is a reply to message #290134] Thu, 27 December 2007 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The output I need is one of the two rows (either is fine).

This is inconsistent with your previous post:
Quote:

All the fields called are used in the output.

p.id as I said is meaningless in your output, why displaying it?

It is inconsistent with this other sentence:
Quote:

But both rows are still returned since familyid is distinct. Familyid is required in the output.

In your output you lose familyid 30.

First you have to validate your requirements, then you code.

Regards
Michel

[Updated on: Thu, 27 December 2007 11:11]

Report message to a moderator

Re: Problem with selecting distinct values [message #290151 is a reply to message #290140] Thu, 27 December 2007 13:55 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
1. The rows returned are the result of the columns requested via the select statement. Please clarify what is inconsistent about that.

2. The id is used in the web page that displays the selected results. Familyid, as well as all the other selected columns are displayed. The web page should only show each upc once regardless of whether it is in the product table more than once with a different familyid. That's not what the query is currently doing.

3. I need each upc to only be returned in the result set once. Loosing the row with family id 30 is fine, but I still need all the selected columns displayed for each distinct upc.

If this would be better accomplished via web page code and not via sql, let me know.
Re: Problem with selecting distinct values [message #290152 is a reply to message #290151] Thu, 27 December 2007 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The rows returned are the result of the columns requested via the select statement. Please clarify what is inconsistent about that.

The sentences I quoted.

I only ask one thing: with the 2 data you gave what is the result?

Quote:

The web page should only show each upc once regardless of whether it is in the product table more than once with a different familyid.

So this is just a display issue and not an Oracle one, do it in your web server.

Quote:

Loosing the row with family id 30 is fine,

This is not what you said before.

Regards
Michel
Re: Problem with selecting distinct values [message #290153 is a reply to message #290152] Thu, 27 December 2007 14:54 Go to previous messageGo to next message
KMWord
Messages: 11
Registered: December 2007
Junior Member
What does "I only ask one thing: with the 2 data you gave what is the result?" mean? Are you looking for something other than the result sets I included?

My original post stated "Is there a way to form the query to return each upc only once regardless of whether there are rows in the product table with the same upc but different familyid". I've always wanted to 'display' each upc once and had hoped Oracle could accomplish that. None of my posts state that I want all rows returned for a upc, one per familyid.
Re: Problem with selecting distinct values [message #290155 is a reply to message #290153] Thu, 27 December 2007 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None of your posts state clearly what you want, more they are inconsistent:
Quote:

But both rows are still returned since familyid is distinct. Familyid is required in the output.

Quote:

Loosing the row with family id 30 is fine,


Quote:

What does "I only ask one thing: with the 2 data you gave what is the result?" mean?

You gave an example with data, what is the result you want with this data?

Anyway, it is not a SQL issue (Oracle returns SQL result it does not display anything), it is a client issue.

Regards
Michel
Re: Problem with selecting distinct values [message #290157 is a reply to message #290153] Thu, 27 December 2007 17:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
If you want to return only one row per UPC and you don't care which row it is, then using an analytic function like row_number, as previously suggested will do that for you, as demonstrated below. The partition by clause that it uses has nothing to do with table partitions. You will need to use to_char on your clob columns as you seem to have already discovered. You must partition by the upc and order by something that is distinct, like the rowid of the table that contains the upc, in order to retrieve only one row per upc.

-- original with multiple rows per upc:
SCOTT@orcl_11g> select
  2  			       distinct to_char(p.upc),p.id,
  3  			       pn.productname,
  4  			       p.productsize,
  5  			       pf.label,
  6  			       p.weight,
  7  			       to_char(p.description),
  8  			       pt.label, p.familyid
  9  		     from
 10  			       product p,
 11  			       productname pn,
 12  			       productformats pf,
 13  			       producttypes pt
 14  		     where
 15  			       p.productnameid = pn.id (+)
 16  		     and       p.formatid = pf.id (+)
 17  		     and       p.typeid = pt.id (+)
 18  		     and       p.brandid = 1
 19  /

TO_CHAR(P.UPC)
----------------------------------------------------------------------------------------------------
        ID
----------
PRODUCTNAME
----------------------------------------------------------------------------------------------------
PRODUCTSIZE
----------------------------------------------------------------------------------------------------
LABEL
----------------------------------------------------------------------------------------------------
WEIGHT
----------------------------------------------------------------------------------------------------
TO_CHAR(P.DESCRIPTION)
----------------------------------------------------------------------------------------------------
LABEL
----------------------------------------------------------------------------------------------------
  FAMILYID
----------
000001
         2
name A
big
format A
10 oz
description A
type A
        30

000001
         1
name A
big
format A
10 oz
description A
type A
        29


2 rows selected.

SCOTT@orcl_11g>


-- revision with 1 arbitrary row per upc:
SCOTT@orcl_11g> SELECT UPC, ID, PRODUCTNAME, PRODUCTSIZE, PF_LABEL, WEIGHT, DESCRIPTION, PT_LABEL, FAMILYID
  2  FROM   (select to_char(p.upc) AS UPC,
  3  		    p.id,
  4  		    pn.productname,
  5  		    p.productsize,
  6  		    pf.label AS PF_LABEL,
  7  		    p.weight,
  8  		    to_char(p.description) AS DESCRIPTION,
  9  		    pt.label AS PT_LABEL,
 10  		    p.familyid,
 11  		    ROW_NUMBER () OVER (PARTITION BY TO_CHAR (P.UPC) ORDER BY P.ROWID) AS RN
 12  	     from   product p,
 13  		    productname pn,
 14  		    productformats pf,
 15  		    producttypes pt
 16  	     where  p.productnameid = pn.id (+)
 17  	     and    p.formatid = pf.id (+)
 18  	     and    p.typeid = pt.id (+)
 19  	     and    p.brandid = 1)
 20  WHERE  RN = 1
 21  /

UPC
----------------------------------------------------------------------------------------------------
        ID
----------
PRODUCTNAME
----------------------------------------------------------------------------------------------------
PRODUCTSIZE
----------------------------------------------------------------------------------------------------
PF_LABEL
----------------------------------------------------------------------------------------------------
WEIGHT
----------------------------------------------------------------------------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
PT_LABEL
----------------------------------------------------------------------------------------------------
  FAMILYID
----------
000001
         1
name A
big
format A
10 oz
description A
type A
        29


1 row selected.

SCOTT@orcl_11g>

Re: Problem with selecting distinct values [message #290363 is a reply to message #290157] Fri, 28 December 2007 09:15 Go to previous message
KMWord
Messages: 11
Registered: December 2007
Junior Member
That works perfectly. Thank you so much for sending an example.
Previous Topic: nested tables (split from hijacked thread)
Next Topic: passing variable to a select query
Goto Forum:
  


Current Time: Mon Dec 05 15:04:05 CST 2016

Total time taken to generate the page: 0.06265 seconds