Sorting UNION query

[miguel.guerreiro]

Hi folks,

I wanted to create a query that returns a list of company names sorted
alphabetically, and then I just add 1 line (constant) to the top of the
list so my results should look like:
_____________
MyStringHere
Axxxx
Bxxxxx
Cxxxx
Dxxxxx
_____________

I created the following query that works and does everything I want,
except the sorting is screwed up. It ignores the ORDER BY clause and
doesn't sort anything.
SELECT DISTINCT 'MyStringHere' FROM tblAddress
UNION ALL
(SELECT Company FROM tblAddress ORDER BY Company ASC);

So, this query actually gives me a result that looks like:
____________
MyStringHere
Cxxxx
Axxxx
Dxxxxx
Bxxxx

Any idea on how I can get this UNION to be sorted the way I need it ?


Thanks.

 

[Guest]

Hi.

If I remember correctly, you can only sort on the fields in the first part
of a Union query. So, reverse the order (i.e. Select Company, and Union
Select MyStringHere) and your Companies will be sorted.

However, MyStringHere will sort under "M", so I would suggest changing it to
something like (MyStringHere), so that not only will it sort to the top of
the list, but it will also be more distinguishable from the other entries.

-Michael

 

[Pieter Wijnen]

I Like to make a Duplicate of Oracle's DUAL for these purposes (single row,
single column)

CREATE TABLE DUAL (X CHAR(1));
INSERT INTO DUAL VALUES('X');

SELECT 'MyString' AS CompanyName, 0 AS SORT FROM DUAL
UNION ALL
SELECT Company, 1 AS SORT FROM tblAddress
ORDER BY 2,1

HTH
Pieter

 

[Pieter Wijnen]

I Like to make a Duplicate of Oracle's DUAL for these purposes (single row,
single column)

CREATE TABLE DUAL (X CHAR(1));
INSERT INTO DUAL VALUES('X');

SELECT 'MyString' AS CompanyName, 0 AS SORT FROM DUAL
UNION ALL
SELECT Company, 1 AS SORT FROM tblAddress
ORDER BY 2,1

HTH
Pieter

Hi.

If I remember correctly, you can only sort on the fields in the first part
of a Union query. So, reverse the order (i.e. Select Company, and Union
Select MyStringHere) and your Companies will be sorted.

However, MyStringHere will sort under "M", so I would suggest changing it
to
something like (MyStringHere), so that not only will it sort to the top of
the list, but it will also be more distinguishable from the other entries.

-Michael

--

 

[M. Guerreiro]

Thanks for your help guys.
Michael: My query already does what you propose, so your solution
didn't really solve my problem. I don't want the constant string to be
sorted as part of the rest of the list of query results.

Pieter: I like your solution, and would implement it as a last resort
because it requires the creation of tables.

I was able to find a weird but ellegant solution that sorts without you
telling it to. It's mind blowing, so I'll be sharing it here for the
benefit of all. The combination of a UNION statement with a UNION ALL
does the trick. The proponent of this solution knew about the internal
behavior of the SQL engine. Check out the fact that I never instruct
the query to sort, yet it does, because of the WHERE clause in the
UNION. Also, instead of using a DISTINCT command I used TOP 1 which is
much faster since it requires no internal sorting.

SELECT TOP 1 'MyCompanyName' FROM tblAddress
UNION ALL
(SELECT Company1 FROM tblAddress WHERE Company1>=''
UNION SELECT Company1 FROM tblAddress WHERE Company1>='' );

Thanks again guys.

 

[Pieter Wijnen]

The DUAL table is a good approach though, as it will work on any SQL-89
compatible plattform & you'd only have to make it once per application ;-)

Pieter

Thanks for your help guys.
Michael: My query already does what you propose, so your solution
didn't really solve my problem. I don't want the constant string to be
sorted as part of the rest of the list of query results.

Pieter: I like your solution, and would implement it as a last resort
because it requires the creation of tables.

I was able to find a weird but ellegant solution that sorts without you
telling it to. It's mind blowing, so I'll be sharing it here for the
benefit of all. The combination of a UNION statement with a UNION ALL
does the trick. The proponent of this solution knew about the internal
behavior of the SQL engine. Check out the fact that I never instruct
the query to sort, yet it does, because of the WHERE clause in the
UNION. Also, instead of using a DISTINCT command I used TOP 1 which is
much faster since it requires no internal sorting.

SELECT TOP 1 'MyCompanyName' FROM tblAddress
UNION ALL
(SELECT Company1 FROM tblAddress WHERE Company1>=''
UNION SELECT Company1 FROM tblAddress WHERE Company1>='' );

Thanks again guys.

--