JCP1975 JCP1975 -4 years ago 71
SQL Question

Custom SORT BY SQL

I'm new to the community but have referenced it many times in the past. I have an issue I'm trying to overcome in Access, specifically with a SORT BY issue in SQL.

Long story short, I need to create a report based on the results of several different queries. I used a Union query to skirt the "Query is too complex" issue. The results of the query aren't in the order I'd like them, though.

Since this UNION query is not based on one specific table, rather the results of many queries, I'm not able to sort by a specific column header.

I want to sort the results by the way they are written in the SQL statement. Can anyone provide some insight to how to do this? I've attempted several different ways but always end up with an error message. Here's the code, and any help is greatly appreciated.

SELECT [Aqua-Anvil_Total].Expr1
FROM [Aqua-Anvil_Total];
UNION SELECT [Aqua-Reslin_Total].Expr1
FROM [Aqua-Reslin_Total];
UNION SELECT [Aqua_Zenivex_Total].Expr1
FROM [Aqua_Zenivex_Total];
UNION SELECT [Aqualuer_20-20_Total].Expr1
FROM [Aqualuer_20-20_Total];
UNION SELECT [Avalon_Total].Expr1
FROM [Avalon_Total];
UNION SELECT [BVA_13_Total].Expr1
FROM [BVA_13_Total];
UNION SELECT [Deltagard_Total].Expr1
FROM [Deltagard_Total];
UNION SELECT [Envion_Total].Expr1
FROM [Envion_Total];
UNION SELECT [Scourge_18-54_Total].Expr1
FROM [Scourge_18-54_Total];
UNION SELECT [Zenivex_E20_Total].Expr1
FROM [Zenivex_E20_Total];

Answer Source

This uses union all instead of union, so if you are using union to remove duplicates, there would be more work to do after this.

select Expr1 
from (
  select [Aqua-Anvil_Total].Expr1, 0 as sort
  from [Aqua-Anvil_Total]
  union all select [Aqua-Reslin_Total].Expr1, 1 as sort
  from [Aqua-Reslin_Total]
  union all select [Aqua_Zenivex_Total].Expr1, 2 as sort
  from [Aqua_Zenivex_Total]
  union all select [Aqualuer_20-20_Total].Expr1, 3 as sort
  from [Aqualuer_20-20_Total]
  union all select [Avalon_Total].Expr1, 4 as sort
  from [Avalon_Total]
  union all select [bva_13_Total].Expr1, 5 as sort
  from [bva_13_Total]
  union all select [Deltagard_Total].Expr1, 6 as sort
  from [Deltagard_Total]
  union all select [Envion_Total].Expr1, 7 as sort
  from [Envion_Total]
  union all select [Scourge_18-54_Total].Expr1, 8 as sort
  from [Scourge_18-54_Total]
  union all select [Zenivex_E20_Total].Expr1, 9 as sort
  from [Zenivex_E20_Total]
) as u
order by u.sort
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download