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
Stanton_RouxAsked:
Who is Participating?
 
Aaron TomoskyConnect With a Mentor Technology ConsultantCommented:
You could order by the second letter of the region desc. But this is getting really weird.
0
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.