Re: Sorting Order by Table?
- From: John Schaeffer <amerar@xxxxxxx>
- Date: Sun, 22 Mar 2009 16:28:38 -0700 (PDT)
On Mar 22, 2:26 pm, ddf <orat...@xxxxxxx> wrote:
On Mar 22, 12:10 pm, John Schaeffer <ame...@xxxxxxx> wrote:
On Mar 22, 12:50 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
<ame...@xxxxxxx> a écrit dans le message de news: 73d9495e-79aa-4b19-9923-c690c4057...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
| We have this query, which is actually put together dynamically within
| some PL/SQL code and opened via REF CURSOR. The WHERE clause is built
| using regular IF / THEN logic to put together the criteria and then
| read via REF CURSOR.
|
| SELECT fund_name, ticker, class_rank, nav, total_return_ytd,
| total_return_1yr, total_return_3yr
| FROM (SELECT mf.fund_name, mf.ticker,
| class_rank, dp.nav, NVL(fm.total_return_ytd,
| -9999999999) total_return_ytd,
| NVL(fm.total_return_1yr, -9999999999) total_return_1yr,
| NVL(fm.total_return_3yr, -9999999999) total_return_3yr ,
| ROW_NUMBER()
| OVER (ORDER BY class_rank, total_return_6mt DESC) cnt
| FROM mutual_fund mf, fund_master fm, fund_daily_prices dp,
| fund_styles fs,
| performance_rank pr, fund_classes fc
| WHERE fm.fund_id = mf.m_fund_id
| AND mf.ticker = dp.ticker(+)
| AND mf.ticker = pr.ticker(+)
| AND mf.ticker = fs.ticker(+)
| AND mf.ticker = fc.ticker(+)
| AND dp.nav IS NOT NULL
| AND fm.obj_descr = 'Growth'
| ORDER BY class_rank ASC, total_return_1yr DESC)
| WHERE cnt BETWEEN 1 AND 10;
|
| Here is the hard part: When certain parameters are passed into the
| code, I want to add another table to the FROM clause and another set
| of criteria to the WHERE clause. That is easy, BUT, the records which
| get pulled from the added table, those need to appear on top of the
| result set.
|
| So, for example, say I am selecting people from a set of tables based
| on age, and ordering them by last name. Now, when certain criteria is
| passed, I want to select certain people from an additional table and
| put them at the top of the result set.
|
| Not sure this can be done easily. I really want to avoid some huge
| UNION queries with some awkward sorting column.......
|
| Thanks!!
order by new_table.column nulls last, ...
or
order by decode(new_table.column, null, 1, 0), ...
Regards
Michel
I can give that a try, but how is that going to 'force' the values
from the extra table to the top? Say I have these values:
TABLE 1
John Doe 34
Robert Smith 27
Jeff Jones 42
Now, I select those from a table and order it by their age. Forget
about joining for now. Now, say the special parameter gets passed
into the package so I need to add the extra table which contains the
following:
TABLE 2
Kevin Johnson 36
I do not care about the sort order for the ending result set, I want
him at the top of the result set, above any other record.......over
riding the sort order.....
I do not think that is easily done without duplicating the entire long
select statement with some sort of UNION and weird sorting. I'm
hoping to avoid that.......- Hide quoted text -
- Show quoted text -
Yes, you'll likely need a UNION query but you might not need
additional sorting; a UNION ALL, with the queries in the 'proper'
order (meaning your additional criteria table data listed first in the
UNION ALL construct) should return the additional results first in the
list. See here:
http://oratips-ddf.blogspot.com/2008/07/preserving-union.html
David Fitzjarrell
Triple post, sorry. But I think I found it. Basically a structure
like this:
SELECT .....
FROM (
SELECT....ordr, ROW_NUMBER() OVER (ORDER BY ordr, .......) cnt
FROM (
SELECT ......, 'A' ordr
FROM table1, table2, table3
WHERE ......)
UNION
SELECT ....., 'B'
FROM table1, table2
WHERE .....))
WHERE cnt BETWEEN 1 AND 10;
Using a hard coded value within the inner queries to force the sorting
order. Ugly, but I guess it will have to do......
.
- Follow-Ups:
- Re: Sorting Order by Table?
- From: Shakespeare
- Re: Sorting Order by Table?
- References:
- Sorting Order by Table?
- From: amerar@xxxxxxx
- Re: Sorting Order by Table?
- From: Michel Cadot
- Re: Sorting Order by Table?
- From: John Schaeffer
- Re: Sorting Order by Table?
- From: ddf
- Sorting Order by Table?
- Prev by Date: Re: Sorting Order by Table?
- Next by Date: How to pass Oracle certification exam easily?
- Previous by thread: Re: Sorting Order by Table?
- Next by thread: Re: Sorting Order by Table?
- Index(es):
Relevant Pages
|
