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.

On Oct 8, 2:26*pm, artme... (AT) yahoo (DOT) com wrote:
Opps, not 50 items returned, I meant 25.......

On Oct 8, 2:26*pm, artme... (AT) yahoo (DOT) com wrote:
Opps, not 50 items returned, I meant 25.......

On Oct 8, 2:26*pm, artme... (AT) yahoo (DOT) com wrote:
Opps, not 50 items returned, I meant 25.......

On Oct 8, 2:26*pm, artme... (AT) yahoo (DOT) com wrote:
Opps, not 50 items returned, I meant 25.......

On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artmerar (AT) yahoo (DOT) com wrote:

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

On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artmerar (AT) yahoo (DOT) com wrote:

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

On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artmerar (AT) yahoo (DOT) com wrote:

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

On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artmerar (AT) yahoo (DOT) com wrote:

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

On Oct 8, 4:51*pm, sybra... (AT) hccnet (DOT) nl wrote:
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.......