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

SQL Query Help

Hi There

I need a SQL query that will display the following table

Region      Contact
Cape Town      Fred
CapeTown      Joe
Cape Town      Anna
Durban      John
Durban      Mary
Johannesburg      Frank

and display the results like this

Region      Contact
Durban      John
Durban      Mary
Johannesburg      Frank
Cape Town      Anna
CapeTown      Fred
Cape Town      Joe

Thanks
Stanton
0
Stanton_Roux
Asked:
Stanton_Roux
1 Solution
 
Lukasz ChmielewskiCommented:
this would be the first one
select  region, contact from TABLE order by region asc, contact desc
is there any pattern in the second one ?
0
 
tigin44Commented:
is this what you are looking for

SELECT Region,      Contact
FROM yourtable
ORDER BY Region   ASC,   Contact ASC
0
 
Stanton_RouxAuthor Commented:
Sorry confusing question the first table is the way the data is stored in the DB.

I want that data to be displayed like the results in the second table.
In the second table the people are in DESC order but the regions are mixed up.

So I am looking for a query to display the data the way it is in the second table
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tigin44Commented:
is there any order rule for listing the regions...
0
 
Stanton_RouxAuthor Commented:
no it must be displayed exaclty as it is in the second table
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
You could order by the second letter of the region desc. But this is getting really weird.
0
 
tigin44Commented:
does such a listing can help you?
SELECT Y.Region,  Y.Contact
FROM yourtable Y
INNER JOIN (SELECT Region, ROW_NUMBER() OVER (ORDER BY Contact) AS regionOrder
			FROM (SELECT Region, MIN(Contact) AS Contact
				  FROM yourTable) A) X ON Y.Region = X.Region
ORDER BY X.regionOrder DESC,   Y.Contact ASC

Open in new window

0
 
Stanton_RouxAuthor Commented:
Thanks Aaron

Heres the solution

SELECT Region,      Contact
FROM Test
order by Substring(LEFT(Region,2),2,Len(Region)-3) DESC ,Contact ASC
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: 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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now