Need Union Query to run and return the results in the same query o

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written a union query as follows to determine the best location match
based from a table of possible gas analyses. The union query should be
running them in top to bottom order with the exact match first down to the
broadest match. However, when it lists the results it sorts them by the
order it finds them in the master table and NOT in order of the query. Is
there a solution?

SELECT ALL [Laboratory Number],[Well Name], [Sample Date Start], [Sampling
Point]
FROM [Gas Analysis exact UWI match]
UNION SELECT ALL [Laboratory Number], [Well Name], [Sample Date
Start],[Sampling Point]
FROM [Gas Analysis UWI location match]
UNION SELECT ALL [Laboratory Number], [Well Name], [Sample Date
Start],[Sampling Point]
FROM [Gas Analysis Section match]
UNION SELECT ALL [Laboratory Number], [Well Name], [Sample Date
Start],[Sampling Point]
FROM [Gas Analysis Township match];
 
You haven't asked for any specific order. Insert a ORDER BY clause following
your last WHERE clause:
ORDER BY [Sampling Point];

Assuming [Sampling Point] is the appropriate field. In any case the field
name has to match one of the fields in the first SELECT statement. That's a
future reference factoid, since it isn't an issue in this case.


HTH,
 
One possible solution:
SELECT "A" AS SortOrder, [Laboratory Number],[Well Name],
[Sample Date Start], [Sampling Point]
FROM [Gas Analysis exact UWI match]
UNION SELECT ALL "B" AS SortOrder, [Laboratory Number],
[Well Name], [Sample Date Start],[Sampling Point]
FROM [Gas Analysis UWI location match]
UNION SELECT ALL "C" AS SortOrder, [Laboratory Number],
[Well Name], [Sample Date Start],[Sampling Point]
FROM [Gas Analysis Section match]
UNION SELECT ALL "D" AS SortOrder, [Laboratory Number],
[Well Name], [Sample Date Start],[Sampling Point]
FROM [Gas Analysis Township match]
ORDER BY SortOrder ;
 
Back
Top