Re: Sorting Order by Table?

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 23 Mar 2009 09:06:04 +0100
Message-ID: <49c742eb$0$183$e4fe514c_at_news.xs4all.nl>

John Schaeffer schreef:

> 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......
> 
> 
> 

I think this is quite a normal way of (un)sorting joins.... adding a 'source' column

Shakespeare Received on Mon Mar 23 2009 - 03:06:04 CDT