asked on

sql order by question

Here is my table of values

Market      MarketName

2001      Albuquerque
2002      Birmingham
2003      Central Alabama
2004      Central Florida
2005      Central/NW Austin
2006      Denver
2007      DFW Metro
2008      DFW North
2009      DFW South
2010      DFW West
2011      Eastern
2012      Gulf Coast
2013      Mobile
2014      NE Florida (Jaxs)
2015      North Alabama
2016      North Houston
2017      Northern
2018      San Antonio/SE Austin
2019      South Houston
2020      Southeast Alabama
2021      Southern
4001      Alabama /Florida
4002      Arizona/Colorado
4003      North Texas/New Mexico
4004      South Central Texas
10001      Corporate

I need this ordered as follows: First, I need every record with a market of 4000 and higher order by that the field market in descending order. Then I need all markets less than 4000 ordered alphabetically by the field marketname. Is it possible to order two different ways with the same table of data?

Answer_Me

Try this
Select * from tablename
Where Market >=4000
Order by Market desc
Union all
Select * from tablename
Where Market <4000
order by marketname

dchau12

ASKER

Nope. You cant have two order by clauses in a union statement.

MohanKNair

select Market,MarketName,
case when market>4000 then '0'||lpad(to_char(market),25,'0') else '1'||MarketName END sort_order
FROM <table>
ORDER BY sort_order;

dchau12

ASKER

I am working with sql server. I'm not sure what query language this query was written in Mohan.

MohanKNair

This was written for Oracle. But I think case statement is there in SQL Server also.

arun04

select * from (
Select t.*,'o'||to_char((select max(market) from table_name)-market) sort_col from tablename t
Where Market >=4000
Union all
Select t.*,'1'||marketname sort_col from tablename t
Where Market <4000
)
order by sort_col

dchau12

ASKER

What does a pipe '|' translate to is sql server?

ASKER CERTIFIED SOLUTION

arun04

membership

This solution is only available to members.

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

Start Free Trial

dchau12

ASKER

That was pretty awesome arun. Good job.