Re: Sorting Order by Table?

From: John Schaeffer <amerar_at_iwc.net>
Date: Sun, 22 Mar 2009 16:28:38 -0700 (PDT)
Message-ID: <894984d9-9915-4a2d-b34f-d7fe6e86d773_at_j39g2000yqn.googlegroups.com>

On Mar 22, 2:26 pm, ddf <orat..._at_msn.com> wrote:
> On Mar 22, 12:10 pm, John Schaeffer <ame..._at_iwc.net> wrote:
>
>
>
> > 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.......- 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...... Received on Sun Mar 22 2009 - 18:28:38 CDT