Re: Sorting Order by Table?

<amerar_at_iwc.net> a écrit dans le message de news: 73d9495e-79aa-4b19-9923-c690c4057439_at_q9g2000yqc.googlegroups.com...
|
| 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 Received on Sun Mar 22 2009 - 00:50:45 CDT