• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

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.


select currency from currency order by id union select '-- Please Select --' as currency from currency

Open in new window

0
techques
Asked:
techques
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you want to have Order by In your UNION Statement, then use order by in the last UNION query statement instead of the first query.
select currency from currency 
union select '-- Please Select --' as currency from currency
order by id 

Open in new window

0
 
Pratima PharandeCommented:
select currency from (
select currency from currency, id
union
select '-- Please Select --' as currency,0 from currency) X
order by id
0
 
techquesAuthor Commented:
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'.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Pratima PharandeCommented:
id field must exists in currency table........... is it ?
0
 
Anthony PerkinsCommented:
select      id,
            currency
from      currency

UNION ALL

select      0,
            '-- Please Select --'
from currency
order by id


0
 
techquesAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
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
0
 
Anthony PerkinsCommented:
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
0
 
techquesAuthor Commented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should help you out:
SELECT currency
FROM (
select id,currency
from currency
UNION ALL
select 0,'-- Please Select --'
) temp
order by id

Open in new window

0
 
techquesAuthor Commented:
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?
0
 
Anthony PerkinsCommented:
It is a typo, missing a comma.  It should be:
Select      currency
From      (
      select      id,
                        currency
      from            currency

      UNION ALL

      select      0,
                        '-- Please Select --'
       ) d

order by id
0
 
techquesAuthor Commented:
both excellent!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now