techques
asked on
How to write union and order in SQL?
Hi
The following SQL has the error:
Incorrect syntax near the keyword 'union'.
If take out order by id, then it works.
However, it does not sort by id.
The following SQL has the error:
Incorrect syntax near the keyword 'union'.
If take out order by id, then it works.
However, it does not sort by id.
select currency from currency order by id union select '-- Please Select --' as currency from currency
select currency from (
select currency from currency, id
union
select '-- Please Select --' as currency,0 from currency) X
order by id
select currency from currency, id
union
select '-- Please Select --' as currency,0 from currency) X
order by id
ASKER
select currency from currency
union select '-- Please Select --' as currency from currency
order by id
it has error:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
select currency from (
select currency from currency, id
union
select '-- Please Select --' as currency,0 from currency) X
order by id
It has error:
Invalid object name 'id'.
union select '-- Please Select --' as currency from currency
order by id
it has error:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
select currency from (
select currency from currency, id
union
select '-- Please Select --' as currency,0 from currency) X
order by id
It has error:
Invalid object name 'id'.
id field must exists in currency table........... is it ?
select id,
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency
order by id
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency
order by id
ASKER
Yes, there is id field in currency table.
select id,
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency
order by id
It can display, but it list out 29 rows of 0, -- Please Select -- duplicated records.
And, I do not need to display id=0 on the result.
select id,
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency
order by id
It can display, but it list out 29 rows of 0, -- Please Select -- duplicated records.
And, I do not need to display id=0 on the result.
Or if you do not want to see id in the selected list, use a derived table as in:
Select currency
From (
select id
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency) d
order by id
Select currency
From (
select id
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency) d
order by id
You are right:
select id,
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
order by id
Or:
Select currency
From (
select id
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
) d
order by id
select id,
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
order by id
Or:
Select currency
From (
select id
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
) d
order by id
ASKER
Select currency
From (
select id
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency) d
order by id
It has error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
From (
select id
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
from currency) d
order by id
It has error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
select id,
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
order by id
It can display properly. But, how to take out the id from the result?
currency
from currency
UNION ALL
select 0,
'-- Please Select --'
order by id
It can display properly. But, how to take out the id from the result?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
both excellent!
Open in new window