Re: Union question

From: Peter Nilsson <airia_at_acay.com.au>
Date: Wed, 15 Oct 2008 21:52:52 -0700 (PDT)
Message-ID: <6eb20c36-dca7-46e8-b0f3-8575d3ad24b8@o40g2000prn.googlegroups.com>

On Oct 10, 4:25 am, artme..._at_yahoo.com wrote:
> ...this is what I came up with, and it works:
>
> SELECT * FROM (
>    SELECT rownum, ticker, comp_name FROM (
>      SELECT ticker, comp_name, 1 ord, ROW_NUMBER()
>      OVER (PARTITION BY DECODE(ticker,NULL,1,0) ORDER BY ticker) cnt
>      FROM master_table
>      WHERE REGEXP_LIKE (UPPER(ticker), '^MOT| MOT')
>      UNION
>      SELECT ticker, comp_name, 2 ord, ROW_NUMBER()
>      OVER (PARTITION BY DECODE(ticker,NULL,1,0) ORDER BY ticker) cnt
>      FROM master_table
>      WHERE REGEXP_LIKE (UPPER(comp_name), '^MOT| MOT')
>      )
>    ORDER BY ord
>    )
> WHERE rownum <= 10;
>
> Any other suggestions?

  with
    x as

      (select distinct
         ticker, comp_name,
         case
           when regexp_like(ticker,    '^mot| mot', 'i') then 1
           when regexp_like(comp_name, '^mot| mot', 'i') then 2
         end x
       from master_table)

  select ticker, comp_name
    from (select x.*, row_number() over (order by x.x) rn

            from x x
           where x.x is not null)

  where rn <= 10;

--
Peter

Received on Wed Oct 15 2008 - 23:52:52 CDT