Re: Union question

From: <artmerar_at_yahoo.com>
Date: Thu, 9 Oct 2008 10:25:23 -0700 (PDT)
Message-ID: <ac900bf5-f80f-4a34-a0ab-4380499b2e5c@q26g2000prq.googlegroups.com>

On Oct 9, 11:43 am, artme..._at_yahoo.com wrote:
> On Oct 8, 4:51 pm, sybra..._at_hccnet.nl wrote:
>
> > On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artme..._at_yahoo.com wrote:
> > >> Problem with this query is that if the symbol and company name both
> > >> meet the criteria, I get duplicates.
>
> > Is this an UNION ALL query?
> > No?
> > So you WON'T get duplicates, as a SET CAN'T have duplicates!
> > Will you ever learn SQL?
> > I won't hold my breath!
> > Apart from that, why do you post everything 2 or 3 times?
> > Are you feeling THAT important?
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Master of personality and people skills, my question was a bit more
> than the duplicates.  If I post something it is because I've spend 3-4
> hours trying to figure it out.
>
> Again, here is the query:
>
> SELECT DISTINCT(ticker), comp_name, ord FROM (
>   SELECT ticker, comp_name, ord FROM (
>     SELECT ticker, comp_name, 1 ord FROM master_table
>     WHERE REGEXP_LIKE (UPPER(ticker), '^MOT| MOT')
>     ORDER BY 3, 1)
>   WHERE rownum <= 10)
> UNION
> SELECT DISTINCT(ticker), comp_name, ord FROM (
>   SELECT ticker, comp_name, ord FROM (
>     SELECT ticker, comp_name, 2 ord FROM master_table
>     WHERE REGEXP_LIKE (UPPER(comp_name), '^MOT| MOT')
>     ORDER BY 3, 1)
>   WHERE rownum <= 10)
> ORDER BY ord, ticker, comp_name;
>
> I'm trying to not only order it without duplicates, which it seems to
> do now, but I want the final result to be 10 or less records.  Problem
> is that the last WHERE clause checking the rownum does not do
> anything.  My thought is because the sub-queries take care of their
> own rownum count.
>
> What I need is to select the tickers, up to 10, AND, select the
> company names, up to 10.   Then limit the total result to 10.  So, if
> there are less than 10 from the first query, it will be made up by the
> second query......
>
> I've been looking at this and analytical functions with no luck.  I
> think this needs to be in one query because I will open it as a ref
> cursor for some php script to grab the results from.
>
> Again, think I came up with this in 5 minutes and posted it?  Think
> again mister.....after 3-4 hours I have the right to post and
> ask.......

Ok, crap. After another hour this is what I came up with, and it works:

SELECT * FROM (
   SELECT rownum, ticker, comp_name FROM (

     SELECT ticker, comp_name, 1 ord, ROW_NUMBER()
     OVER (PARTITION BY DECODE(ticker,NULL,1,0) ORDER BY ticker) cnt
     FROM master_table
     WHERE REGEXP_LIKE (UPPER(ticker), '^MOT| MOT')
     UNION
     SELECT ticker, comp_name, 2 ord, ROW_NUMBER()
     OVER (PARTITION BY DECODE(ticker,NULL,1,0) ORDER BY ticker) cnt
     FROM master_table
     WHERE REGEXP_LIKE (UPPER(comp_name), '^MOT| MOT')
     )

   ORDER BY ord
   )
WHERE rownum <= 10;

Any other suggestions? That is a HUGE mess..... Received on Thu Oct 09 2008 - 12:25:23 CDT