String of Subqueries -- Your suggestions on alternatives

[uparna]

Ok , Here is the sticky situation :-

consider table FLOWERS.

[b]
FLWRID  FLWRLGCYID  FLWRTYP      FLWRNM        LNGTH        WDTH      SLSIND[/b]
1          101          Stemmed          Rose          10"          2"          1
2          102          Stemmed          Rose          20"          3"
3          103          Stemmed          Rose          1"           4"
4          104          Stemmed          Rose          13"          5"
5          105          Delicate         Lily          14"          2"
6          106          Petal            Rose          9.5"         3"
7          107          Delicate         Lily          1"           4"
8          108          Hardy            Tulip         2"           3"          1
9          109          Delicate         Orchid        3"           2"
10         110          Delicate         Tulip         4"           1"
11         111          Petal            Rose          6"           3"          1
12         112          Fragrant         Rose          4.5"         4"
13         113          Petal            Rose          6.2"         3"           
14         114          Fragrant         Rose          4.3"         4"
15         115          Fragrant         Rose          4.1"         4"

Now the requirement is that i should compare the flowers with the sales ind = 1 (STEP A), and see if there are other records
which have the same flower name , but with a different flower type (STEP B). Among these records , i should pick only one
for each flower type and display all details (STEP C).

to illustrate one example:-

Step A --
[b]
FLWRID  FLWRLGCYID  FLWRTYP      FLWRNM        LNGTH        WDTH      SLSIND[/b]
1          101          Stemmed          Rose          10"          2"          1

Step B --
[b]
FLWRID  FLWRLGCYID  FLWRTYP      FLWRNM        LNGTH        WDTH      SLSIND[/b]
6          106          Petal            Rose          9.5"         3"
11         111          Petal            Rose          6"           3"          1
12         112          Fragrant         Rose          4.5"         4"
13         113          Petal            Rose          6.2"         3"           
14         114          Fragrant         Rose          4.3"         4"
15         115          Fragrant         Rose          4.1"         4"

Step C --
[b]
FLWRID  FLWRLGCYID  FLWRTYP      FLWRNM        LNGTH        WDTH      SLSIND[/b]
6          106          Petal            Rose          9.5"         3"
12         112          Fragrant         Rose          4.5"         4"

While the business asks me to pick any random row, i intend to take the one with the min flowerid , so that there is some logic in selection

I would have to write several subqueries , atelast one each for Step A & Step B and 2 for Step C ( one for selecting Min flower id , and one for getting ALL the details for that Min flower id)

I was wondering if anyone had a simpler way to achieve this.

This is a one time cleanup operation , and i can go ahead with the slow solution of subqueries, or even write a procedure...but where there is a chance of learning and improvement in SQL , i want to use it to the max and learn.

Let me know your thoughts.

Thanks for reading!

Regards,
S. Jayaram Uparna .

 

[uparna]

here is the query in the lines of what i am thinking currently....note here that the table has more than 2 mill records, has 15-20 cols.


SELECT * FROM
flowers f1 , flowers f2
WHERE trim(UPPER(f1.FLWRNM)) = trim(UPPER(f1.FLWRNM)) AND
f1.slsind IS NOT NULL AND
f1.FLWRTYP != f2.FLWRTYP AND
f2.flwrid IN
(
SELECT MIN(f2.FLWRID) FROM flowers f1 , flowers f2
WHERE trim(UPPER(f1.FLWRNM)) = trim(UPPER(f1.FLWRNM)) AND
f1.slsind IS NOT NULL AND
f1.FLWRTYP != f2.FLWRTYP
)

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jay,

Your code looks solid to me. And one additional note: You can safely use "=" instead of "IN" for this case since a group function such as "MIN()" returns exactly one row. There is no advantage to changing the code from "IN" to "="...I just wanted you to be aware of the 1-row behaviour of the group-function results.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[uparna]

okie dokie mufasa! if you say its solid, solid it is.

however, rose is just an example i gave above. There are 2mill rows in the table , with over 500,000 flowers , so i think an '=' wont work in this case , unless i refer to the outer query F2 in my inner query . Am i missing something here?

Everytime i sweat over a query , i try to post it here so that i can learn more efficient methods of data-retrieval. In this case , however, i think this is the only way to go (if not for a procedure). Thanks again !


Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jay,

Correct: If there is possibility of returning more than one row from the subquery, then you want to use "IN". But even if you have 20 rows in the "Flowers" table with the same matching, minimum value of "FLWRID" (which you won't since I presume that FLWRID is the unique primary key), what is the maximum number of rows that will return from "MIN(FLWRID)"?...One, right?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[uparna]

omg , you are right. flowerid is a primarykey. here is the corrected query then :-

SELECT * FROM
flowers f1 , flowers f2
WHERE trim(UPPER(f1.FLWRNM)) = trim(UPPER(f1.FLWRNM)) AND
f1.slsind IS NOT NULL AND
f1.FLWRTYP != f2.FLWRTYP AND
f2.flwrid IN
(
SELECT FLWRID FROM
( SELECT FLWRNM,FLWRTYP, MIN(FLWRID)AS FLWRID
FROM flowers f1 , flowers f2
WHERE trim(UPPER(f1.FLWRNM))= trim(UPPER(f1.FLWRNM))AND
f1.slsind IS NOT NULL AND
f1.FLWRTYP != f2.FLWRTYP
)
)








Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Actually, Jay, I still have some issues/suggestions regarding your altered code:

Issue 1) Your code is more complex than it needs to be. You should be able to run as simply as:

SELECT * FROM 
flowers f1 , flowers f2
WHERE trim(UPPER(f1.FLWRNM)) = trim(UPPER(f1.FLWRNM)) AND 
f1.slsind IS NOT NULL AND
f1.FLWRTYP != f2.FLWRTYP AND
f2.flwrid IN
(SELECT MIN(FLWRID)
    FROM    flowers f1 , flowers f2
    WHERE trim(UPPER(f1.FLWRNM))= trim(UPPER(f1.FLWRNM))AND 
    f1.slsind IS NOT NULL AND
    f1.FLWRTYP != f2.FLWRTYP
);

Issue 2: What does this code do for you?:

...WHERE trim(UPPER(f1.FLWRNM))= trim(UPPER(f1.FLWRNM))

Can you give me an example where a non-NULL "FLWRNM" from a specific row in the "flowers" table will not equal itself?

Issue 3: Even if "flowerid" was not a unique primary key and it has 5 duplicate, minimum values such as:

FLOWERID
--------
28
23
23
23
23
23
27
35...

...then if you issue the following query and observer the results, notice only one row returns from the query despite there being 5 equal, minimum rows:

SELECT MIN(FLOWERID) FROM FLOWERS;

MIN(FLOWERID)
-------------
           23

So, you even with multiple, matching minimums, you can use "=" as the comparison operator since there is only one minimum value despite there being five occurrences of the minimum, right?

Let us know your thoughts.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[uparna]

Oops ,

...WHERE trim(UPPER(f1.FLWRNM))= trim(UPPER(f1.FLWRNM))

should have been

...WHERE trim(UPPER(f1.FLWRNM))= trim(UPPER(f2.FLWRNM))

copy-paste problem. funny. .


However ,

(SELECT MIN(FLWRID)
    FROM    flowers f1 , flowers f2
    WHERE trim(UPPER(f1.FLWRNM))= trim(UPPER(f2.FLWRNM))AND 
    f1.slsind IS NOT NULL AND
    f1.FLWRTYP != f2.FLWRTYP
);

will return only one key :- for eg:-

if Stemmed - Roses has (6,11,13) Petal-Rose and (12,14,15) fragrant-Rose , then the query will return only 6....i want both 6 and 12. Moreover, if (8) Hardy Tulip is supposed to return (10) Delicate Tulip , the above subquery will not work , right ? the subquery will only give the minimum-most of all the appearences of ALL flowers in different types.

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jay,

Again, I don't know your data well enough to give you specific answers, but I can definitively tell you that for any SELECT for which you ask for MIN(something) and you do not have a GROUP BY, then you absolutely, positively will receive ONE row per iteration of the SELECT. And since you receive one row with one value, you are able to use the '=' as a relational operator in liew of the 'IN' operator. That's all I'm saying.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[uparna]

i agree Mufasa.....thats exactly what i had tried to explain in my previous post....

thanks again!

Regards,
S. Jayaram Uparna .