| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with Query
On Nov 25, 10:59 am, "Jeff B" <jef..._at_KnoSpam.tds.net> wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
> news:4ed69188-f428-46dd-86f0-8850efb9faa9_at_n20g2000hsh.googlegroups.com...
> > On Nov 25, 9:52 am, "Jeff B" <jef..._at_KnoSpam.tds.net> wrote:
> >> Hi everyone,
>
> >> I have a book table and in that table it has the book tile, publisher,
> >> and
> >> type of book it is. example mystery, scifi, etc...
>
> >> I am trying to write a query that brings back a list of every pair of
> >> books
> >> that have the same publisher and same book type. I have been able to get
> >> the following code to work:
>
> >> select publisher_code, type
> >> from book
> >> group by publisher_code, type
> >> having count(*) > 1;
>
> >> which returns the following results:
>
> >> PU TYP
> >> -- ---
> >> JP MYS
> >> LB FIC
> >> PE FIC
> >> PL FIC
> >> ST SFI
> >> VB FIC
>
> >> I can not figure out how to get the book title and book code for the
> >> books
> >> that this result list represents, everything i have tried throws out an
> >> error.
>
> >> Can someone help?
>
> >> Thanks,
>
> >> Jeff
>
> > I see two possible methods:
> > 1. Slide the SQL statement that you have written into an inline view,
> > join the inline view to your book table, and then use the
> > publisher_code, type columns to drive back into your book table. The
> > join syntax may look like one of the following: (publisher_code, type)
> > IN (SELECT...) or b.publisher_code=ib.publisher_code and
> > b.type=ib.type
> > 2. Use analytical functions (COUNT() OVER...) to determine the number
> > of matches for the same publisher_code, type columns. Then slide this
> > SQL statement into an inline view to retrieve only those records with
> > the aliased COUNT() OVER greater than 1. This has the benefit of
> > retrieving the matching rows in a single pass.
>
> > You will likely find examples of the above approaches in this and the
> > comp.databases.oracle.server group.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Hi Charles,
>
> Thanks for the response. I can not say that I understand everything that
> you were saying. When you say inline view do you mean like this?
>
> select title
> from book
> where publisher_code and type in
> (select publisher_code, type
> from book
> group by publisher_code, type
> having count(*) > 1);
>
> this did not work it threw back the following error:
>
> SQL> select title
> 2 from book
> 3 where type in
> 4 (select publisher_code, type
> 5 from book
> 6 group by publisher_code, type
> 7 having count(*) > 1);
> (select publisher_code, type
> *
> ERROR at line 4:
> ORA-00913: too many values
The above only tries to find a matching TYPE, yet the subquery is returning PUBLISHER_CODE and TYPE - that is the result of this error message. Close to what you need.
>
> SQL> select title
> 2 from book
> 3 where publisher_code, type in
> 4 (select publisher_code, type
> 5 from book
> 6 group by publisher_code, type
> 7 having count(*) > 1);
> where publisher_code, type in
> *
> ERROR at line 3:
> ORA-00920: invalid relational operator
Very close to what you need. However, Oracle expects the column names to be wrapped in () ... like this: where (publisher_code, type) in
The above uses a subquery, which may perform slow on some Oracle releases compared to the use of an inline view. Assume that I have a table named PART, which has columns ID, DESCRIPITION, PRODUCT_CODE, and COMMODITY_CODE, with ID as the primary key. I want to find ID, DESCRIPTION, and COMMODITY_CODE for all parts with the same DESCRIPTION and PRODUCT_CODE, where there are at least 3 matching parts in the group:
The starting point, which looks similar to your initial query:
SELECT
DESCRIPTION,
PRODUCT_CODE,
COUNT(*) NUM_MATCHES
FROM
PART
GROUP BY
DESCRIPTION,
PRODUCT_CODE
HAVING
COUNT(*)>=3;
When the original query is slid into an inline view and joined to the
original table, it looks like this:
SELECT
P.ID, P.DESCRIPTION, P.COMMODITY_CODE
|* 1 | HASH JOIN | | 1 | 1768 | 11525 | 00:00:00.21 | 2748 | 1048K| 1048K| 1293K (0)| | 2 | VIEW | | 1 | 1768 | 1156 | 00:00:00.11 | 1319 | | | | |* 3 | FILTER | | 1 | | 1156 | 00:00:00.11 | 1319 | | | | | 4 | HASH GROUP BY | | 1 | 1768 | 23276 | 00:00:00.08 | 1319 | | | | | 5 | TABLE ACCESS FULL| PART | 1 | 35344 | 35344 | 00:00:00.04 | 1319 | | | | | 6 | TABLE ACCESS FULL | PART | 1 | 35344 | 35344 | 00:00:00.04 | 1429 | | | | -------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - access("IP"."DESCRIPTION"="P"."DESCRIPTION" AND
"IP"."PRODUCT_CODE"="P"."PRODUCT_CODE")
3 - filter(COUNT(*)>=3)
The query format using the subquery looks like this: SELECT
P.ID, P.DESCRIPTION, P.COMMODITY_CODE
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 1 | 11525 | 00:00:00.21 | 2748 | 1048K| 1048K| 1214K (0)| | 2 | VIEW | VW_NSO_1 | 1 | 1768 | 1156 | 00:00:00.12 | 1319 | | | | |* 3 | FILTER | | 1 | | 1156 | 00:00:00.12 | 1319 | | | | | 4 | HASH GROUP BY | | 1 | 1768 | 23276 | 00:00:00.09 | 1319 | | | | | 5 | TABLE ACCESS FULL| PART | 1 | 35344 | 35344 | 00:00:00.04 | 1319 | | | | | 6 | TABLE ACCESS FULL | PART | 1 | 35344 | 35344 | 00:00:00.01 | 1429 | | | | -----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - access("DESCRIPTION"="$nso_col_1" AND "PRODUCT_CODE"="$nso_col_2")
3 - filter(COUNT(*)>=3)
Without allowing the automatic transformations in Oracle 10.2.0.2, the query takes _much_ longer than 0.21 seconds to complete.
The method using analytical functions starts like this: SELECT
P.ID, P.DESCRIPTION, P.COMMODITY_CODE,
P.ID,
P.DESCRIPTION,
P.COMMODITY_CODE,
|* 1 | VIEW | | 1 | 35344 | 11525 | 00:00:00.31 | 1319 | | | | | 2 | WINDOW SORT | | 1 | 35344 | 35344 | 00:00:00.27 | 1319 | 2533K| 726K| 2251K (0)| | 3 | TABLE ACCESS FULL| PART | 1 | 35344 | 35344 | 00:00:00.04 | 1319 | | | | -----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter("NUM_MATCHES">=3)
Note that there is only one TABLE ACCESS FULL of the PART table in the above. The execution time required 0.31 seconds to complete, which is greater than the first two approaches, but that is because the database server is concurrently still trying to resolve the query method using the subquery with no permitted transformations (5+ minutes later).
> SQL> select title
> 2 from book
> 3 where publisher_code and type in
> 4 (select publisher_code, type
> 5 from book
> 6 group by publisher_code, type
> 7 having count(*) > 1);
> where publisher_code and type in
> *
> ERROR at line 3:
> ORA-00920: invalid relational operator
> is this what you were referring too? still not being able to get it. I am
> thinking that i have to be half way to the solution with the first part that
> did bring back the six sets, just cannot figure out how to get the book code
> and titles for each of those six sets?
>
> Again thanks for the help
>
> Jeff
Your first attempt was very close. Keep working at it. Maxim Demenko, who has previously posted very clever solutions to other problems, provides another method to find the solution.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun Nov 25 2007 - 11:26:56 CST
 |
 |