 |
|
|
|
Next: Running SQL Server 2005 SP2 and want to run the s..
|
| Author |
Message |
External
Since: Jul 25, 2008
Posts: 76
|
(Msg. 1) Posted: Wed Oct 08, 2008 12:26 pm
Post subject: Union question
Archived from groups: comp>databases>oracle>misc (more info?)
|
|
|
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. |
|
| Back to top |
|
 | |
External
Since: Jul 25, 2008
Posts: 76
|
(Msg. 2) Posted: Wed Oct 08, 2008 12:34 pm
Post subject: Re: Union question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Oct 8, 2:26 pm, artme....RemoveThis@yahoo.com wrote:
> 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.
Opps, not 50 items returned, I meant 25....... |
|
| Back to top |
|
| |
External
Since: Dec 15, 2007
Posts: 159
|
(Msg. 3) Posted: Wed Oct 08, 2008 6:26 pm
Post subject: Re: Union question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artmerar.DeleteThis@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 >> Stay informed about: Union question |
|
| Back to top |
|
| |
External
Since: Jul 25, 2008
Posts: 76
|
(Msg. 4) Posted: Thu Oct 09, 2008 9:43 am
Post subject: Re: Union question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Oct 8, 4:51 pm, sybra....RemoveThis@hccnet.nl wrote:
> On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artme....RemoveThis@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....... >> Stay informed about: Union question |
|
| Back to top |
|
| |
External
Since: Oct 01, 2008
Posts: 57
|
(Msg. 5) Posted: Thu Oct 09, 2008 10:40 am
Post subject: Re: Union question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Oct 9, 11:43 am, artme... RemoveThis @yahoo.com wrote:
> On Oct 8, 4:51 pm, sybra... RemoveThis @hccnet.nl wrote:
>
> > On Wed, 8 Oct 2008 12:34:03 -0700 (PDT), artme... RemoveThis @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.......
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 >> Stay informed about: Union question |
|
| Back to top |
|
| |
External
Since: Feb 13, 2008
Posts: 11
|
(Msg. 6) Posted: Wed Oct 15, 2008 9:52 pm
Post subject: Re: Union question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Oct 10, 4:25 am, artme....RemoveThis@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 >> Stay informed about: Union question |
|
| Back to top |
|
| |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Profile
Private Messages
Log in
