Re: Sorting Order by Table?
From: John Schaeffer <amerar_at_iwc.net>
Date: Sun, 22 Mar 2009 15:53:53 -0700 (PDT)
Message-ID: <b6a38047-6f2e-4499-9ca0-0b1cb9648823_at_j8g2000yql.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:53:53 -0700 (PDT)
Message-ID: <b6a38047-6f2e-4499-9ca0-0b1cb9648823_at_j8g2000yql.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
I might add, something else that makes this nearly impossible is that the client wants to reture 10 records at a time, or all the records at once. If the webpage is requesting the first 10 records, or all at once, then the results from the additional table need to appear at the top, pushing off any other records at the bottom of the records set to make the result 10 records. Received on Sun Mar 22 2009 - 17:53:53 CDT