?
Solved

SQL query union and sorting

Posted on 2012-08-24
21
Medium Priority
?
591 Views
Last Modified: 2012-08-31
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
Comment
Question by:BlueCrystalMan
  • 6
  • 5
  • 4
  • +2
21 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38329363
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
 
LVL 8

Accepted Solution

by:
stalhw earned 2000 total points
ID: 38329369
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38329399
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BlueCrystalMan
ID: 38329906
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
 
LVL 8

Expert Comment

by:stalhw
ID: 38329980
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38330003
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
 
LVL 8

Expert Comment

by:stalhw
ID: 38330105
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
 

Author Comment

by:BlueCrystalMan
ID: 38330121
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38330178
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
 
LVL 8

Expert Comment

by:stalhw
ID: 38330239
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
 

Author Comment

by:BlueCrystalMan
ID: 38330711
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
 
LVL 8

Expert Comment

by:stalhw
ID: 38330851
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38333257
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
 

Author Comment

by:BlueCrystalMan
ID: 38333450
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38334507
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38334563
Actually, that was answered from the start.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38334720
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38335686
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
 
LVL 8

Expert Comment

by:stalhw
ID: 38336751
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38337396
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
 

Author Closing Comment

by:BlueCrystalMan
ID: 38354464
This solution worked best for us. Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question