Is this query done easier?

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 17 Feb 2009 09:09:28 -0800 (PST)
Message-ID: <dae6e79e-7c89-4dd0-bf6c-fc9833357972_at_n2g2000vbl.googlegroups.com>

We have this query:

SELECT ticker, category_name, fund_name, class_rank, total_return_1yr, expense_ratio, market_value_avg, mgr_start_date FROM (SELECT pr.ticker, pr.category_name, mf.fund_name, fc.class_rank, pr.total_return_1yr, fm.expense_ratio, market_value_avg, mgr_start_date, ROW_NUMBER()

      OVER (PARTITION BY category_name ORDER BY class_rank) cnt
      FROM mutual_fund mf, fund_classes fc, performance_rank pr,
fund_master fm
      WHERE pr.category_name = 'Foreign'
        AND pr.ticker = mf.ticker
        AND mf.ticker = fc.ticker
        AND mf.m_fund_id = fm.fund_id)

WHERE cnt <= 4
ORDER BY class_rank, total_return_1yr DESC, expense_ratio, mgr_start_date DESC;

What I need to do is add the information from the data also passed in. something like this:

SELECT ticker, category_name, fund_name, class_rank, total_return_1yr, expense_ratio, market_value_avg, mgr_start_date FROM (SELECT pr.ticker, pr.category_name, mf.fund_name, fc.class_rank, pr.total_return_1yr, fm.expense_ratio, market_value_avg, mgr_start_date, ROW_NUMBER()

      OVER (PARTITION BY category_name ORDER BY class_rank) cnt
      FROM mutual_fund mf, fund_classes fc, performance_rank pr,
fund_master fm
      WHERE pr.category_name = 'Foreign'
        AND pr.ticker = mf.ticker
        AND mf.ticker = fc.ticker
        AND mf.m_fund_id = fm.fund_id)

WHERE cnt <= 4
UNION
SELECT 'PRMSX', category_name, fund_name, class_rank, total_return_1yr, expense_ratio, market_value_avg, mgr_start_date FROM (SELECT pr.ticker, pr.category_name, mf.fund_name, fc.class_rank, pr.total_return_1yr, fm.expense_ratio, market_value_avg, mgr_start_date, ROW_NUMBER()

      OVER (PARTITION BY category_name ORDER BY class_rank) cnt
      FROM mutual_fund mf, fund_classes fc, performance_rank pr,
fund_master fm
      WHERE pr.category_name = 'Foreign'
        AND pr.ticker = mf.ticker
        AND mf.ticker = fc.ticker
        AND mf.m_fund_id = fm.fund_id)

WHERE cnt <= 1
ORDER BY class_rank, total_return_1yr DESC, expense_ratio, mgr_start_date DESC;

Is there an easier way to do this without the UNION? Something shorter? It is the ORDER BY that seems to cause issues. It removes the passed in data because of the values in the query. We want 4 records, PLUS the record passed in.....

Possible in a shorter query? We're still playing right as we post this.

Regards,

Jim Received on Tue Feb 17 2009 - 11:09:28 CST