Union question

#21

On Oct 9, 11:43*am, artme... (AT) yahoo (DOT) com wrote:

Quote:

On Oct 8, 4:51*pm, sybra... (AT) hccnet (DOT) nl wrote:

On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artme... (AT) yahoo (DOT) 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.......

You should be trying this:

SELECT m.ticker, m.comp_name, m.ord
FROM
(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)) m
where rownum <= 10
ORDER BY m.ord, m.ticker, m.comp_name;

which should return the results you require. Note that DISTINCT does
not operate on a single column (it's not a function), it operates on
the entire select list.

David Fitzjarrell

#22

On Oct 10, 4:25*am, artme... (AT) yahoo (DOT) com wrote:

Quote:

...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

#23

On Oct 10, 4:25*am, artme... (AT) yahoo (DOT) com wrote:

Quote:

...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

#24

On Oct 10, 4:25*am, artme... (AT) yahoo (DOT) com wrote:

Quote:

...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

#25

On Oct 10, 4:25*am, artme... (AT) yahoo (DOT) com wrote:

Quote:

...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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Union question

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Union question - 10-09-2008 , 12:40 PM

Re: Union question - 10-15-2008 , 11:52 PM

Re: Union question - 10-15-2008 , 11:52 PM

Re: Union question - 10-15-2008 , 11:52 PM

Re: Union question - 10-15-2008 , 11:52 PM