Order by in Union Query

[netrusher]

Below is my SQL on a query. My issue is it does not arrange
by count Descending order like the SQL shows. Before I
joined them in a Union Query each one of them individually
sorts correctly.

SELECT TOP 5 "1-3T" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Count(*) DESC
union
SELECT TOP 5 "4-7T" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813","D024","C879")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
union
SELECT TOP 5 "7-9TP" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory;

 

[AlexCuse]

For a UNION, you can only add an order by at the very end of the query. So you could delete your order by and then add

order by Totals desc

Hope this helps,

Alex



[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Thanks Alex,

When I do that it Mixes up the Truck Identifiers.

I want to keep all the:
1-3T
4-7T
6-9TP
together and sor the totals descending for each of them.

 

[PHV]

What about using UNION ALL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[orna]

Order By Track, Totals desc

 

[netrusher]

Thanks everyone:

I am using the code as below but have another issue. I am
getting different totals when I run each of the queries
below separately except for the Top Query. It is like for
for the Top Query I get an exact match of results when I
run the individual query. When I run the individual Query
for the 2nd and 3rd queries of this Union Query the results
are different? Can anyone tell me why or what to changed?

SELECT TOP 5 "1-3T" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Count(*) DESC
union
SELECT TOP 5 "4-7T" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813","D024","C879")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
union
SELECT TOP 5 "6-9TP" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Truck, Totals Desc;

 

[orna]

PHV advise you to use union all and you should

 

[netrusher]

Orna,

Blessings to you! I guess I should have stated that I used Union all and did not see any change in the results. So what difference is UNION ALL supposed to make? Do I make other changes when I use UNION ALL? Please see code below.

Also, please see my last posting concerning the results! I do not get the same results as I get when I run the queries Individually.

SELECT TOP 5 "1-3T" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Count(*) DESC
UNION ALL
SELECT TOP 5 "4-7T" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813","D024","C879")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
UNION ALL 
SELECT TOP 5 "6-9TP" AS Truck, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Truck, totals DESC;

 

[netrusher]

Anybody still looking at this posting??? I still need help!!!

 

[lespaul]

the difference between UNION and UNION ALL is the removal of duplicates. If the second query returns a record that is also in the first query a UNION will remove the duplicate record from the result set and a UNION ALL will leave the duplicate record in the result set.

As far as why the results are different in the individual queries from the results in the UNION query....have no idea without running each query individually and comparing the results from the UNION query and trying to identify what is unique about the missing records. And you are the only one who can do that...there's no way any of us can figure that out for you.


Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[netrusher]

Thanks Miss Leslie, I appreciate the advice. As far as the query comparisons I will attemt to do that. I can only say at this time the only results that are the same are the ones in the first part of the Union query. The other two are not even close at this time.