CODEWITHSUNDEEP
sqlms-access
4e4el
4e4el

Reputation: 13

How to repair "order by" after union of 2 selects from 1 tables

I have a dropDownList on my form, where i need to have union of values from 2 colums of table [ost].

Type of this columns is currency. I have russian version of access, default value of curency in "rur" and i need "uah". I need to change format and save "order by".

I use this query:

(SELECT distinct FORMAT([Sum1] ,'# ##0.00" uah.";-# ##0.00" uah."') FROM ost) 
Union 
(SELECT distinct FORMAT([Sum2],'# ##0.00" uah.";-# ##0.00" uah."') FROM ost)
ORDER BY 1

Upvotes: 1

Views: 1406

Answers (2)

Guffa
Guffa

Reputation: 700910

If you mean that you want to sort by the original value, then you have to include that in the result:

(SELECT distinct FORMAT([Sum1] ,'# ##0.00" uah.";-# ##0.00" uah."'), [Sum1] FROM ost) 
Union 
(SELECT distinct FORMAT([Sum2],'# ##0.00" uah.";-# ##0.00" uah."'), [Sum2] FROM ost)
ORDER BY 2

Upvotes: 1

MvanGeest
MvanGeest

Reputation: 9661

I don't exactly get what you want. If you need to order afterwards:

I'm not sure if you need a table alias, but wouldn't an outer SELECT work?

SELECT *
FROM (
    (SELECT distinct FORMAT([Sum1] ,'# ##0.00" uah.";-# ##0.00" uah."') FROM ost) 
    UNION
    (SELECT distinct FORMAT([Sum2],'# ##0.00" uah.";-# ##0.00" uah."') FROM ost)
)
ORDER BY 1

If you need to preserve order, doesn't this simple query work?

(SELECT distinct FORMAT([Sum1] ,'# ##0.00" uah.";-# ##0.00" uah."') FROM ost ORDER BY 1)
UNION
(SELECT distinct FORMAT([Sum2],'# ##0.00" uah.";-# ##0.00" uah."') FROM ost ORDER BY 1)

But there's always "one more problem" with any SQL I write, so please check if it actually fulfills the requirements.

Upvotes: 1

Related Questions