• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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