Complex order by clauses

I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)

I thought I'd be able to do something like:

ORDER BY (price != 0), price

thinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.

any suggestions?

thanks!

try to do SOMETHING LIKE IN this EXAMPLE:

select

*into #testfrom(select 129.89 price)aa

insert

into #testselect 19.89

insert

into #testselect 1.89

insert

into #testselect 49.89

insert

into #testselect 39.89

insert

into #testselect 29.89

insert

into #testselect 0

insert

into #testselect 0

select

*from #TEst

order

by
case price
When 0then 1
else
0
END,
price

drop

table #test

Complex ORDER BY - possible?

I want to sort recrords by two columns, but would like to order them by a fixed value in the first column.

Example.

I have an employee database, and want to sort by SKILL, YEARS_EXPERIENCE.

But I want a specific skill listed first, then all other skills.

Such as (I just made this up):

SELECT * from emp_master order by (SKILL='C#', YEARS_EXPERIENCE DESC), (SKILL <> 'C#', YEARS_EXPERIENCE DESC).

So my results would be:

C#, 10
C#, 7
C#, 5
ASP.NET, 10
ASP.NET, 9
ASP.NET, 5
SQL, 5
SQL, 4
VB, 5
VB, 3

This is handy for 'near' matches where I want a preferred result to filter to the top, but all results in some order.

Is this possible?One thing you might consider is returning the results for C# and sorting those, then UNION joining that to a result set that does NOT contain C#.|||UNION still just sorts by the common sort criteria, unless I am doing something wrong.

If I use two SQL statements, SQL A chooses 'C#' and years DESC, the SQL B chooses <> 'C#' and years DESC, and then UNION, they all come back in order of the years DESC without the C# being first on the list.|||You should be able to use a CASE statement:

SELECT * from emp_master
ORDER BY
CASE [Skill] WHEN 'C#' THEN 0 ELSE 1 END,
Skill,Years_Experience DESC

|||Yup, that'll definitely do it! :)|||EOM

Compatability : SQL Server 2005 Management Studio

Hello Everyone,

I have a Web Host which uses SQL Server 2000. I can use their interface and make tables and procedures, but I wanted to use some sort of graphical tool...Is it possible to use SQL Server 2005 Studio with SQL 2000.

I'm really new to SQL Server as my past experience is largely with Oracle....and there for the same purpose I can use SQL Navigator or TOAD....

IF SQL Server 2005 Studio wont work can someone please suggest a tool I can use to create tables and procedures on the remote server..

Thanks,

Harsimrat

Hi,

yes you can use SQL Server 2005 Management Studio for administring SQL2k databases, I do that also with my host databases in the web. If you don′t have SQL Server 2005 Management Studio you can also use one of the tools mentioned here:

http://www.aspfaq.com/2442

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Awesome,

Thanks

Harsimrat