asked on

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

Lukasz Chmielewski

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 ?

tigin44

is this what you are looking for

SELECT Region, Contact
FROM yourtable
ORDER BY Region ASC, Contact ASC

Stanton_Roux

ASKER

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

tigin44

is there any order rule for listing the regions...

Stanton_Roux

ASKER

no it must be displayed exaclty as it is in the second table

ASKER CERTIFIED SOLUTION

Aaron Tomosky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tigin44

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

Stanton_Roux

ASKER

Thanks Aaron

Heres the solution

SELECT Region, Contact
FROM Test
order by Substring(LEFT(Region,2),2,Len(Region)-3) DESC ,Contact ASC