Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Sorting a union select statement?

Posted on 2011-09-07
11
214 Views
Last Modified: 2012-06-27
Hello experts.  Hoping for some help. My Statement below isn't working.

SELECT DISTINCT State
FROM         cities
WHERE     (Country = 'Canada')
Order By State
UNION
SELECT DISTINCT State
FROM         cities
WHERE     (Country = 'us')
Order by State

What I am trying to do is have all of the Canadian provinces listed FIRST, alphabetized, followed by all the US states, alphabetized.  Maybe Union is the wrong way to go?

0
Comment
Question by:arthurh88
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 9

Expert Comment

by:AriMc
ID: 36499206
Try this:

SELECT DISTINCT state
FROM cities
WHERE (Country = 'Canada') OR (Country = 'us')
ORDER BY Country, State
0
 
LVL 18

Expert Comment

by:lludden
ID: 36499302
Or
SELECT DISTINCT state
FROM cities
WHERE Country IN ('Canada','us')
ORDER BY Country, State

I like to use IN rather than OR in case you add other conditions you don't have to worry about grouping conditions.
0
 

Author Comment

by:arthurh88
ID: 36499304
no go.  I got this error:  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36499363
@AriMc - I don't think your order by will work because Country is not in the selection

Try this:

select State from
(
  select distinct State, Country
  from cities
  where Country in ('Canada','us')
  order by Country, State
)
0
 
LVL 9

Expert Comment

by:AriMc
ID: 36499441
@gerwinjansen - That probably depends on the DB-engine. Your query seems to give errors regarding unambiguous aliases. This one works at least in MySQL:

select ns.State from
(
  select distinct State, Country
  from cities
  where Country in ('Canada','us')
  order by Country, State
)  ns

0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36499480
@AriMc - OK :)

@arthurh88 - last one working for you?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36499488
You should use a CASE statement in the ORDER BY.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36499511
Try like this:

SELECT DISTINCT [State]
FROM cities
ORDER BY CASE [Country] WHEN 'Canada' THEN 0 ELSE 1 END, [State]

Open in new window


And you may want to look at GROUP BY as you may want the [Country] involved in case you are using the abbreviations for states as I believe there is at least AL that conflicts.

SELECT [State]
FROM cities
-- WHERE [Country] IN ('Canada', 'US') 
GROUP BY [Country], [State]
ORDER BY CASE [Country] WHEN 'Canada' THEN 0 ELSE 1 END, [State]

Open in new window


Uncomment the WHERE clause if you have other countries in your cities table.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36499518
Oh, and first query would work also if you use GROUP BY. I over "thunk" it since 'Canada' alphabetically sorts before 'US' anyway, you don't really need the CASE.

Change http:#a36499206 to:
SELECT [State]
FROM cities
WHERE [Country] IN ('Canada', 'US') 
GROUP BY [Country], [State]
ORDER BY [Country], [State]
;

Open in new window


Hope that helps!
0
 

Author Closing Comment

by:arthurh88
ID: 36499709
yes this one worked!

I tried

SELECT     State
FROM         (SELECT DISTINCT TOP (100) PERCENT State, Country
                       FROM          cities
                       WHERE      (Country IN ('Canada', 'us'))
                       ORDER BY Country, State) AS derivedtbl_1

but it did not work, it mixed US and Canadian states together.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36499775
Yes, sort in a view which the derived table is acting like doesn't really guarantee sorting on the outer query. It is the reason why sorting views is useless. Glad that helped.

Best regards and happy coding,
Kevin
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 37
Filtering characters in an SQL field 2 16
SQL trigger 5 21
Finding gaps or missing dates in a date range using TSQL 3 23
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question