October 19, 2016 at 5:46 am
Hi there
I have written a simply SQL query to output various items of data including the country that someone is from - all very easy!
However, I now need to order the data within the SQL statement dependent on whether the countries are in Europe or not. This fact is not held in the database, so I need to order the data based on the fact e.g. France, Germany, UK etc are in Europe within the SQL statement itself.
I appreciate that the following script isn't SQL, but it might help to explain what I'm trying to achieve:
ORDER BY Country WHERE Country IN (France, Germany, Ireland) AS 'Europe' ELSE 'Rest of World'.
I hope that makes sense. The output of my data currently shows first_name, surname, email address, country.
Any help on potential ordering would be very helpful.
Many thanks
Jon
October 19, 2016 at 6:07 am
Does this do what you need?
CREATE TABLE #example
(
first_nameVARCHAR(15)
,surnameVARCHAR(20)
,email_addressVARCHAR(60)
,countryVARCHAR(20)
)
INSERT INTO #example VALUES
('Andy','Capp','AndyCapp@thecouch.ie','Ireland')
,('Asterix','Thegaul','DesMenhirs@obelix.fr','France')
,('Hexe','Lilli','Spell@witches.de','Germany')
,('Paddington','Bear','Marmalade@sandwiches.pe','Peru')
,('blinky','bill','Up@bluegumtree.au','Australia')
SELECT
*
FROM #example e
ORDER BY
CASE WHEN country IN('Ireland'
,'France'
,'Germany')
THEN 1
ELSE 2
END
DROP TABLE
#example
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 19, 2016 at 6:20 am
That's perfect, thank you 🙂
October 19, 2016 at 6:26 am
You're welcome. Happy to help 🙂
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 19, 2016 at 7:03 am
What happens when you get a row from Spain? or Portugal? or any other country in Europe?
You need to create a Countries table to store that information and, preferably, a Continents table as well. Here is the example completed with what I just stated.
CREATE TABLE Continents(
ContinentID int CONSTRAINT PK_Continent PRIMARY KEY,
ContinentName varchar(20)
);
INSERT INTO Continents
VALUES
(1, 'Europe')
,(2, 'America')
,(3, 'Asia')
,(4, 'Africa')
,(5, 'Oceania');
CREATE TABLE Countries(
CountryID int CONSTRAINT PK_Countries PRIMARY KEY,
CountryName varchar(20),
ContinentID int CONSTRAINT FK_Countries_Continents FOREIGN KEY REFERENCES Continents(ContinentID)
);
INSERT INTO Countries
VALUES
(1, 'Ireland',1)
,(2,'France',1)
,(3,'Germany',1)
,(4,'Peru',2)
,(5,'Australia',5);
CREATE TABLE #example
(
first_nameVARCHAR(15)
,surnameVARCHAR(20)
,email_addressVARCHAR(60)
,countryint CONSTRAINT FK_Example_Countries FOREIGN KEY REFERENCES Countries(CountryID)
);
INSERT INTO #example
VALUES
('Andy','Capp','AndyCapp@thecouch.ie',1)
,('Asterix','Thegaul','DesMenhirs@obelix.fr',2)
,('Hexe','Lilli','Spell@witches.de',3)
,('Paddington','Bear','Marmalade@sandwiches.pe',4)
,('blinky','bill','Up@bluegumtree.au',5);
SELECT e.first_name
,e.surname
,e.email_address
,c.CountryName
FROM #example e
JOIN Countries c ON e.country = c.CountryID
ORDER BY c.ContinentID;
DROP TABLE
#example, Countries, Continents;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply