Re: Sorting Order by Table?

From: John Schaeffer <amerar_at_iwc.net>
Date: Sun, 22 Mar 2009 10:10:03 -0700 (PDT)
Message-ID: <c577f02b-88a8-4ce2-ac84-2f564a156ecd_at_y9g2000yqg.googlegroups.com>

On Mar 22, 12:50 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

> <ame..._at_iwc.net> a écrit dans le message de news: 73d9495e-79aa-4b19-9923-c690c4057..._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

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....... Received on Sun Mar 22 2009 - 12:10:03 CDT