Re: Sorting Order by Table?
From: John Schaeffer <amerar_at_iwc.net>
Date: Sun, 22 Mar 2009 15:29:34 -0700 (PDT)
Message-ID: <6713eea0-8db0-404a-9525-85c3f37d59fd_at_w9g2000yqa.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
Date: Sun, 22 Mar 2009 15:29:34 -0700 (PDT)
Message-ID: <6713eea0-8db0-404a-9525-85c3f37d59fd_at_w9g2000yqa.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
Seems to be an interesting link. I'm playing with the query a bit to see if I can tweak it. But, this is not an easy thing. Received on Sun Mar 22 2009 - 17:29:34 CDT