How to sort values within SELECT statement with ORDER BY in a unioned query

[hondaman900]

I have a query that works well, but I get syntax errors when I try to add an ORDER BY clause to sort on a value column for each table before joining it. I know I can use the designer and subreport bands to achieve what I want, but the subreports introduce formatting (padding between sections) that I can't remove and can't have in the report, so handling the data in the Query allows for correct formatting and easier summing of the values in each section. Just need the line items ranked by value.

Here's the query:

SELECT "ASSETS & INVESTMENTS", "Monetary Assets" , ma.created_by_id, ma.monetary_asset_name, ma.monetary_asset_type, ma.monetary_asset_value Asset_Value, ma.monetary_statement_date FROM forge.monetary_assets as maWHERE ma.created_by_id =  $P{user_id}  AND ma.monetary_ownership_type = 'Yours' UNION ALLSELECT "ASSETS & INVESTMENTS", "Investments", il.created_by_id,  il.investment_name, ia.investment_account_type, il.investment_sale_price_fmv Asset_Value, ia.investment_last_statement_date FROM forge.investment_line_items as ilJOIN forge.investment_accounts as iaON ia.id = il.investment_institution_name_idWHERE il.created_by_id =  $P{user_id}  AND ia.investment_ownership = 'Yours' UNION ALLSELECT "ASSETS & INVESTMENTS", "Real Estate", ra.created_by_id, ra.real_name, ra.real_type, ra.real_current_value Asset_Value, ra.real_last_appraisal_date FROM forge.real_property_assets as raWHERE ra.created_by_id =  $P{user_id}  AND ra.real_asset_ownership = 'Yours' UNION ALLSELECT  "ASSETS & INVESTMENTS", "Personal Property", pa.created_by_id, pa.personal_name, pa.personal_type, pa.personal_current_value Asset_Value, pa.personal_last_appraised_date  FROM forge.personal_property_assets as paWHERE pa.created_by_id =  $P{user_id}  AND pa.personal_ownership = 'Yours'UNION ALLSELECT "INSTALLMENT DEBT, MORTGAGES & OTHER LIABILITIES", "Mortgages & Real Estate Debt", ra.created_by_id, ra.real_name, ra.real_type, -ABS(ra.real_amount_owed) Asset_Value, ra.real_last_appraisal_date FROM forge.real_property_assets as raWHERE ra.created_by_id =  $P{user_id}  AND ra.real_asset_ownership = 'Yours' UNION ALLSELECT "INSTALLMENT DEBT, MORTGAGES & OTHER LIABILITIES", "Installments Debts Liabilities" , idl.created_by_id, idl.liability_name, idl.liability_paid_for, -ABS(idl.liability_account_balance) Asset_Value, idl.liability_last_payment_date FROM forge.installments_debts_liabilities as idlWHERE idl.created_by_id =  $P{user_id}  AND idl.liability_ownership = 'Yours' UNION ALLSELECT  "INSTALLMENT DEBT, MORTGAGES & OTHER LIABILITIES", "Attorney Fees", af.created_by_id, af.attorney_name, af.attorney_bill_status, -ABS(af.attorney_amount) Asset_Value, af.attorney_payment_date FROM forge.attorney_fees as afWHERE af.created_by_id =  $P{user_id}  AND af.attorney_fee_ownership = 'Yours' [/code]

That gives me a nice report, but I want to rank the line items per group in DESC order. I can add an ORDER BY Asset_Value DESC clause to the end of the query, and I do get all line items ranked by value, but the organization of the groups get's messed up. I need to rank by value within each group, maintaining the grouping. In straight SQL I could do this in each SELECT statement using TOP, but this gives a syntax error in Studio.

Anyone have a solution to this please?

Here's the report output from the above query:

Thanks in advance

[tblalock34gmail.com]

You can wrap your original sql statment in an in line view and create a type sort column for your sections to keep them in the right order.  Something like this...select * from (SELECT "ASSETS & INVESTMENTS" , "Monetary Assets", 5 type_sort, ma.created_by_id, ma.monetary_asset_name, ma.monetary_asset_type , ma.monetary_asset_value Asset_Value, ma.monetary_statement_date FROM forge.monetary_assets AS maWHERE ma.created_by_id =  $P{user_id}  AND ma.monetary_ownership_type = 'Yours' UNION ALLSELECT "ASSETS & INVESTMENTS", "Investments", 10 type_sort il.created_by_id,  il.investment_name, ia.investment_account_type, il.investment_sale_price_fmv Asset_Value, ia.investment_last_statement_date FROM forge.investment_line_items AS ilJOIN forge.investment_accounts AS iaON ia.id = il.investment_institution_name_idWHERE il.created_by_id =  $P{user_id}  AND ia.investment_ownership = 'Yours' 

) r

order by type_sort, Asset_Value desc