SQL "order by" in sub query

Borat_Sagdiyev

Hi folks, this has been wrecking my head and I can't find an answer to it.

Basically, I have three different tables that I want to select from, and I want to order the values in each table independant of the other tables ie

select column1, column2 from table1
union
select column1, column2 from table2
union
select column1, column2 from table3

The above code functions as expected. But what I want to do is the following:

select column1, column2 from table1 order by column3
union
select column1, column2 from table2 order by column4
union
select column1, column2 from table3 order by column5

This is syntactically incorrect, because the order by clause must appear at the very end of the whole query AFAIK.

Does anyone know of a way to get around this? I have tried inserting a select statement after the from in each segment but that doesn't work either.

Any ideas?

krinpit

Are the column names (ie: column1, column2 and column3) identical in all 3 tables?

Borat_Sagdiyev

No, but I'm using an alias so that the output for each sql statement has the same column name. ie

select column1 as whatever1, column2 as whatever2 from table1
union
select column1 as whatever1, column2 as whatever2 from table2
union
select column1 as whatever1, column2 as whatever2 from table3

Beano

you cant do it that way. What you could do is create a temp table with the same coulmns as you are currently selecting as well as an identity column. Insert the values in to the temp table in the order you want. Then do a select from the temp table ordered by the identity column. A bit longwinded but its the best I can come up with. This all assumes you are using SQL Server.

krinpit

Does this work:

select column1, column2 from table1 tblA
union
select column1, column2 from table2 tblB
union
select column1, column2 from table3 tblC
Order by tblA.column3, tblB.column4, tblC.Column5

Borat_Sagdiyev

krinpit wrote:

Does this work:

select column1, column2 from table1 tblA
union
select column1, column2 from table2 tblB
union
select column1, column2 from table3 tblC
Order by tblA.column3, tblB.column4, tblC.Column5

Don't know, good thinking though, I hadn't tried that, will do now.

Borat_Sagdiyev

Beano wrote:

you cant do it that way. What you could do is create a temp table with the same coulmns as you are currently selecting as well as an identity column. Insert the values in to the temp table in the order you want. Then do a select from the temp table ordered by the identity column. A bit longwinded but its the best I can come up with. This all assumes you are using SQL Server.

I'm using Sybase SQL Anywhere. I haven't used temporary tables before unfortunately, but will look into it.

Does this work:

select column1, column2 from table1 tblA
union
select column1, column2 from table2 tblB
union
select column1, column2 from table3 tblC
Order by tblA.column3, tblB.column4, tblC.Column5

Just tried this, didn't work because it tries to order the whole result set on tblA.column3 first before moving onto the tblcolumn4 bit.

Thanks for your help folks.

bonkey

Borat_Sagdiyev wrote:

The above code functions as expected. But what I want to do is the following:

select column1, column2 from table1 order by column3
union
select column1, column2 from table2 order by column4
union
select column1, column2 from table3 order by column5

This is syntactically incorrect, because the order by clause must appear at the very end of the whole query AFAIK.

Does anyone know of a way to get around this? I have tried inserting a select statement after the from in each segment but that doesn't work either.

Any ideas?

Hmmm.

The first thing that springs to mind is that you probably should be using UNION ALL
rather than UNION. This is because UNION will remove dpulicate rows, and as a result it doesn't make sense to talk about sorting "per table". Should a duplicate occur, only one of the duplicated rows gets output.

Imagine a case where there is a column1/column2 value-pair which exists in each of the three tables. Only one copy of this will be output using UNION. Is this output from table1, table2, or table3? Logically, thats undefined.

Maybe you know you can't have duplicates, but either which way, I'd still suggest UNION ALL for clarity and surity.

Now...on to the problem....

Lets assume that column3, column4 and column5 are all the same datatype. What I'd do then is :

select column1, column2, 1 as tableNo, column3 as sortCol from table1 
union
select column1, column2, 2 as tableNo, column4 as sortCol from table2 
union
select column1, column2, 3 as tableNo, column5 as sortCol from table3 
order by tableNo, sortCol

maybe you don't want those extra two columns in your output. In that case, wrap this in another query :

select column1, column2 
from (
  select column1, column2, 1 as tableNo, column3 as sortCol from table1 
  union
  select column1, column2, 2 as tableNo, column4 as sortCol from table2 
  union
  select column1, column2, 3 as tableNo, column5 as sortCol from table3 
) 
order by tableNo, sortCol

And, if the datatypes are different, well thats no big deal either :

select column1, column2, 1 as tableNo, column3, null as column4, null as column5 from table1 
union
select column1, column2, 2 as tableNo, null as column3, column4, null as column5 from table2 
union
select column1, column2, 3 as tableNo, null as column3, null as column4, column5 from table3 
order by tableNo, column3, column4, column5

bonkey

I should be smacked.

After recommending UNION ALL, I go and not use it myself.

My only defence is that by using the "tableNo" field, I guarantee that the same output can *never* be generated from two tables. However, I still should have used it.

Bad bonkey. No biscuit.

Borat_Sagdiyev

bonkey wrote:

I should be smacked.

After recommending UNION ALL, I go and not use it myself.

My only defence is that by using the "tableNo" field, I guarantee that the same output can *never* be generated from two tables. However, I still should have used it.

Bad bonkey. No biscuit.

Thanks for your help man, but I have already implemented the temporary table example. Works like a dream. Thanks again for your help people.

Beano

Borat_Sagdiyev wrote:

Thanks for your help man, but I have already implemented the temporary table example. Works like a dream. Thanks again for your help people.

WOOHOO!!!

Having said that, on mature reflection, Bonkeys suggestions was less work than mine. But at least with mine you got to learn about temporary tables, which was a bonus.