Union question

Hopefully this is not a dup post. I had to kill my last one to do some typos.

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, '^JK| JK')) 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, 25 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. If I find the answer, I'll let everyone know. Received on Wed Oct 08 2008 - 14:43:03 CDT