Re: Union question

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....... Received on Thu Oct 09 2008 - 11:43:53 CDT