Re: Union question
Date: Wed, 8 Oct 2008 12:34:03 -0700 (PDT)
On Oct 8, 2:26 pm, artme..._at_yahoo.com wrote:
> I'm still playing with this one and looking for some solution in the
> Oracle docs, but, can I have a UNION query and tell it which rows to
> return and the order to return them in? We're on 10g R2.
> SELECT symbol, comp_name, top FROM (
> SELECT symbol, comp_name, 1 top FROM master_table
> WHERE REGEXP_LIKE (symbol, '^JK| JK')
> SELECT symbol, comp_name, 2 top FROM master_table
> WHERE REGEXP_LIKE (comp_name, '^JKA| JKA'))
> WHERE rownum <= 25
> ORDER BY top, ticker;
> Basically I want to select as many as I can from the first query, and
> have them ordered. If it does not meet the number of records to be
> returned, 50 in this example, I want to include records from the
> second query, and have them ordered. If the first does meet the
> number criteria, then the second query is not needed. Additionally, I
> want the n records to be ordered regardless on whether it uses results
> from either or both queries.
> Problem with this query is that if the symbol and company name both
> meet the criteria, I get duplicates. I use the 1 and 2 to make sure
> the symbols get ordered first.
> I'll need to put this into one select statement.....just the
> restrictions of our setup and application. We'll be opening this up
> as a cursor: OPEN data FOR v_query;
> I'm still searching examples and documentation, but I've never had the
> reason to do something like this. If I find the answer, I'll let
> everyone know.
Opps, not 50 items returned, I meant 25....... Received on Wed Oct 08 2008 - 14:34:03 CDT