• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

SQL query union and sorting

I have a massive union query that I need to sort by each individual part. I can’t seem to get it to work, though, even using the UNION ALL. Basically (in English, not SQL, for simplicity):

Select * from TableA ORDER BY LastName

UNION

Select * from TableB ORDER BY Employee

UNION

Select * FROM TableC ORDER BY Hostess

The only way to get that to work is to stick in an ORDER BY 8 at the end of the query, but each query needs to have different sort criteria, as it displays in different tabs in our smartphone app.

Now… we’ve tried the UNION ALL and the ALIAS option, but that didn’t work, either. It did change the way the data came up in the result set, but it still didn’t sort on LastName, say.

Any insight? This will need to work for access and SQL Server.
0
BlueCrystalMan
Asked:
BlueCrystalMan
  • 6
  • 5
  • 4
  • +2
1 Solution
 
CluskittCommented:
SELECT *
FROM (Select *,'1'+LastName OrderCol from TableA
UNION
Select *,'2'+Employee from TableB
UNION
Select *,'3'+Hostess FROM TableC) t
ORDER BY OrderCol
0
 
stalhwCommented:
PS: previous answer will have unexpected behavior if one of the sort field is a null value.

you can do something like this:
Select *, LastName as SortField from TableA
UNION
Select *, Employee as SortField from TableB
UNION
Select *, Hostess as SortField FROM TableC 
ORDER BY SortField

Open in new window

But the real question is why are you doing UNION if in the end all 3 queries are displayed in 3 different places ?
You probably should do 3 different queries...

And if you want one table after the other, yet each ordered you can do:
Select *, 1 as TableNum, LastName as SortField from TableA
UNION
Select *, 2 as TableNum, Employee as SortField from TableB
UNION
Select *, 3 as TableNum, Hostess as SortField FROM TableC 
ORDER BY TableNum, SortField

Open in new window

0
 
Dale FyeCommented:
I would agree with stalhw's comment
But the real question is why are you doing UNION if in the end all 3 queries are displayed in 3 different places ?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
BlueCrystalManAuthor Commented:
The data is being delivered to our smartphone app. There are five separate schedules, each one on a tab. All of the data needs to be delivered at the same time, and it is parsed by the app according to its type. That part is working.

The user needs to be able to select the sort field for each of the five tabs. The may all be sorting on last name, which is no problem, and your solution up there works. But if tab A is sorted by last name, and tab B is sorted by in time, and tab C sorted by In Date, it doesn't work to sort by one individual column, which is why we need the ability to sort within the individual query pieces.
0
 
stalhwCommented:
Still I don't see why you app couldn't do 5 queries instead of one.
But that's you choice.

The solution of adding a select field for sorting on each subquery is the way to go, or you can do the sorting in your app, but that is probably not the best solution.

If you don't like adding 2 fields, you can do it with just one (but need to get rid of Null values), but you'll have a problem since I don't think there a function with the same name in Access and MSSQL to get rid of nulls, so simplest solution would be sorting on 2 fields.

If we had the real queries, instead of adding a 'TableNum' field, we could probably use the 'Type' you a reffering to, that must be a field, right?
0
 
Dale FyeCommented:
to expand on stalhw's earlier post.

If you are allowing your users to select the fields to sort by, you are going to have to build the SQL dynamically.  However, you are going to have to construct the sort fields so that they all have the same datatype (text probably).  The way I've done that below is to use the Format( )  function to convert time and date datatypes into strings which sort nicely, within a given TabNumber construct.

Select *, 1 as TabNum, [LastName] as SortField from TableA
UNION
Select *, 2 as TabNum, Format([TimeField], "hh:mm:ss") as SortField from TableB
UNION
Select *, 3 as TabNum, Format([DateField], "yyyy-mm-dd") as SortField FROM TableC
ORDER BY TableNum, SortField

If I were doing this, I would create a predefined format for each field that might be selected as a tab sort order, then apply that format as I dynamically build my query.
0
 
stalhwCommented:
One problem... Format(xx,yy) does not exist in MSSQL. And the way to do it in MSSQL would be CAST or CONVERT, but that doesn't exist in ACCESS.

So could you be more specific on the requirement that this works in both access and mssql?

Again, the good way to do this would be to do 5 queries... If you can do 1 query, you should be able to do 5... And it would simplify thing too, I mean if a user changes the order of 1 of the 5 subquery, you would just need to redo that one, not redo the query for all 5.
0
 
BlueCrystalManAuthor Commented:
Unfortunately, when passing data to the smartphone, because the data is coming from the user's own server, through our server via a webservice, there is a timing consideration, so handing off multiple queries isn't an option. I know it can be done in multiple queries, but we can't do it that way.

The desktop app controls the sort... they set the sort order there. The smartphone receives the data already sorted.

As suggested above, and mentioned in my first post, we can sort based on one single column, but we want to give the users the ability to sort the schedules as they see fit. We considered the use of a temporary table receiving the data from each of the five recordsets, and then offloading that to the app, and that would work for one single user, but with multiple users, there are data collisions.
0
 
CluskittCommented:
Why don't you store the data internally in 5 different tables and then manipulate them at will? Instead of feeding the query directly to the datasource (as it seems you're doing), create 5 table objects and fill each one with the tables. That way, you can close your connection and it's a piece of cake to sort them. Retrieving 5 tables in a row will take pretty much the same time as retrieving all at once.
0
 
stalhwCommented:
Ok, like Cluskitt said, what prevents you from sorting the data in the app?

And can you know if it's Access or MSSQL before you build the query?
Because if you insist on doing this with a UNION, and user defined sort order, you will need to build a different query depending on whether the source is MSSQL or Access. Because Union usually expects the same types of column...

And are you saying you want to allow you users to sort on multiple column for each subquery?
Like sorting TableA on Lastname, Firstname ?
I know in MSSQL we can easely convert almost any type to varchar, and from there we can build one column for sorting (concatenating multiple columns if necessary)

But I'm not sure that can be done as easely in Access, Cluskitt seems more familiar with Access, he can probably answer that.
0
 
BlueCrystalManAuthor Commented:
The application that controls the data gathering (and sort) is VB, and we can do anything with it... including formatting it for SQL Server, so it works for both SQL and Access.

It is just much easier to play with the data before shipping it to the app and trying to work with it there. We assumed sort it first and just let the app display it as it receives it, but we did not expect the sorting to be so involved.
0
 
stalhwCommented:
ok so MSSQL:  (you can + as many fields as needed)
Select *, '1-'+isnull(convert(varchar,Field1),' ')+' '+isnull(convert(varchar,Field2),' ') as SortField from TableA
UNION
Select *, '2-'+isnull(convert(varchar,Field1),' ')+' '+isnull(convert(varchar,Field2),' ') as SortField from TableB
UNION
*...*
ORDER BY SortField

Open in new window

ACCESS: (You'll have to test this, I'm not that familiar with access)
Select *, '1-' & NZ(CSTR(Field1),' ') & ' ' & NZ(CSTR(Field2),' ') as SortField from TableA
UNION
Select *, '2-' & NZ(CSTR(Field1),' ') & ' ' & NZ(CSTR(Field2),' ') as SortField from TableB
UNION
*...*
ORDER BY SortField

Open in new window


But the problem with these queries is that any numeric field converted to string will not be sorted based on numeric value, but based on the first digit...
so 111 will be sorted before 9...

Again, it would be so much easier to do 1 query per table...
0
 
Anthony PerkinsCommented:
But the problem with these queries is that any numeric field
Even if it is all character data it is a problem if the columns have different lengths.  So if you want to do it this way, you will have to make all the lengths the same, convert all the numeric values to a right justified character expression of the same length and convert all the datetime to the format yyyymmdd hh:mm:ss.

I still don't understand what is wrong with using ORDER BY 8 or whatever.  Are you not building the query on the fly?
0
 
BlueCrystalManAuthor Commented:
The app receives data in a specific order to display it. There are ten fields being sent.

The client may want Schedule1 of 5 sorted on 8, Schedule2 of 5 sorted on 2, Schedule3 sorted on 5, etc.

Yes, it works if we use the sort on one field for all five queries (remember, five different schedules), as I stated in the first post. The question was could each of the five be sorted individually, but it appears they can't be.
0
 
Anthony PerkinsCommented:
The question was could each of the five be sorted individually, but it appears they can't be.
Yes they can, as previously alluded to here http:#a38330851 by adding a sort column and if it is a single column it is trivial.
0
 
CluskittCommented:
Actually, that was answered from the start.
0
 
Anthony PerkinsCommented:
Absolutely.  But it does not have to be a derived table (which I am not even sure is an option with MS Access, only in SQL Server).

And again as alluded to previously, it gets a tad more complicated if the data types are different.
0
 
CluskittCommented:
In terms of efficiency, the best way to handle this is to store the data in 5 public tables within the app code. The way it is now, you get all the data at once, but each time a user changes the sort order, you have to make another query. Storing it locally with persistent table objects allows you to manipulate the data at will without needing to re-query the server.
0
 
stalhwCommented:
Or in case of mssql you could have a stored procedure, that builds a table variable, queries each of the 5 tables individually (sorted), and insert the data in the variable, and then returns the table variable, that can't do that in access...

acperkins:
But the problem with these queries is that any numeric field
Even if it is all character data it is a problem if the columns have different lengths.  So if you want to do it this way, you will have to make all the lengths the same, convert all the numeric values to a right justified character expression of the same length and convert all the datetime to the format yyyymmdd hh:mm:ss.

I don't agree... I walked around that problem, by putting a space ( + ' ' + ) between each fields.
Correct me if I'm wrong, but sorting is done character by character, so space will always be first...
But I do agree if these fields can contain spaces, then it may not order as wished. But for things like firstname/lastname, it should work ok.
0
 
Anthony PerkinsCommented:
Correct me if I'm wrong, but sorting is done character by character, so space will always be first...
That is true:  I had not considered the possibility that columns would not contain spaces.
0
 
BlueCrystalManAuthor Commented:
This solution worked best for us. Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now