Re: Union question

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')
>   UNION
>   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