How do I put a specific table row at the END of my 'ORDER BY' list?

select *
from tableName
where brandID=0, brandName='other'
order by brandName

You should do something like this:

SELECT *
FROM your_table
ORDER BY CASE brandID WHEN brandID=0 THEN 'A' ELSE 'B' END, brandName

SELECT brandID, brandName
FROM
(
 SELECT CASE WHEN brandID = 0 THEN 1 ELSE 0 END ord,
 brandID, brandName
 FROM Brand
) b
ORDER BY ord, brandName ;

This will work on SQL-Server. You can also do this which is standard SQL and will work everywhere:

SELECT 1, your_field1, yourfield2, ....
FROM your_table
WHERE brandID=0 AND brandName='other'
UNION
SELECT 2, your_field2, yourfield2, ....
FROM your_table
WHERE brandID<>0 OR brandName<>'other'
ORDER BY 1, brandName;

P.S: I have now seen the other expert solution which won't work because a syntax error, and also because the filter which takes only the record you mention.

One way to do it is:

select brandID,brandName,
      brandNameTemp=
      case brandName
            when 'other' then 'zzzzz'
      else brandName
      end case
from tbl
order by brandNameTemp

Check the syntax. I do not have MySQL installed on my PC. So I can't verify

use this statement:

select * from baseTable
(
select brandID,brandName,0 as rOrder from table1 where brandID<>0
UNION
select brandID,brandName,1 as rOrder from table1 where brandID=0
) as baseTable
order by baseTable.rOrder,baseTable.brandName

this is exactly what you want

I forgot to say that you shoud substitute "your_table" by the name of your table, and "your_field1, your_field2, ..." by the names of the fields in the table you want to show.

Hope it helps. Regards.

Which of these solutions would perform the fastest?

The fastest should be my first solution
SELECT *
FROM your_table
ORDER BY CASE brandID WHEN brandID=0 THEN 'A' ELSE 'B' END, brandName
But if you are not using SQL-Server maybe you should change the CASE-WHEN statement by the equivalent on your RDBMS.
Which RDBMS are you using? (supposed SQL-Server).
Also, if you want a better performance, change the asterisk (*) by the list of the fields you need.
Hope it helps. Regards

So, regardless, I will have to use 2 SELECT statements?

SELECT *
FROM yr_tbl
ORDER BY CASE WHEN brandID=0  and brandName = 'óther' THEN 'Z' ELSE 'A' END

try this...

Yes, if you use 2 SELECTs, the performance will be worst than using just one SELECT.
Which database system are you using ?

no proble u can use 2 select statement ..... but what it is????

All the given solutions use just one SELECT statement but some use derived table subqueries. A derived table subquery does not necessarily perform any worse or better than a query without it. It can just be used as another syntax for the same operation.

I don't agree with dports. If possible, it's better to avoid subqueries.
Also, if you use a UNION solution, you will perform two internal SELECT operations instead os just one, so performance is not the same.

gplana, That depends on a lot on the DBMS and the optimiser. In most DBMSs subqueries that just derive extra columns are a no-op - they normally use exactly the same plan as putting those columns in the outer part of the query. So it's an unwise generalisation to say that subqueries should be avoided.

I am using mySQL

Ok, if it's MySQL remove the word between CASE and WHEN just as follows
SELECT *
FROM your_table
ORDER BY CASE  WHEN brandID=0 THEN 'A' ELSE 'B' END, brandName
This is the link to the mysql documentation where CASE WHEN statement is explained http://dev.mysql.com/doc/refman/5.0/en/case-statement.html
Regards.

Ok, I'm following everything except the 'A'  'B' part.  Do I actually type 'A' and 'B', or are these just placeholders for some other statement that I should know?  What is this doing exactly?  I read the link, it was a bit vague.  

Ok, I'm following everything except the 'A'  'B' part.  Do I actually type 'A' and 'B', or are these just placeholders for some other statement that I should know?  What is this doing exactly?  I read the link, it was a bit vague.  

did you even tried my code?

select * from baseTable
(
select brandID,brandName,0 as rOrder from table1 where brandID<>0
UNION
select brandID,brandName,1 as rOrder from table1 where brandID=0
) as baseTable
order by baseTable.rOrder,baseTable.brandName

Select allOpen in new window

reza rad,
Your code looks like 2 SELECT statements.  I am trying to accomplish this with just one SELECT statement if possible, for performance sake.

gplana,  How does CASE affect performance?

Can you post your sample data and how you want t o display?

Sure.

brandID=6  brandName=Poorly
brandID=3 brandName=Surf
brandID=12 brandName=Aero
brandID=0 brandName=OTHER

I want it to list like this:

Aero
Poorly
Surf
OTHER

("OTHER" should always be at the end of the list)

did you try this?

select brandName from YourTable order by brandID desc

I apologize.  I wasn't clear.
I want an alphabetical list of brandNames that puts 'OTHER' at the end only (not with the 'O's) :)

if you want use case instead of two queries with where clause and one UNION which i suggested to you , I want to say that you will have more performance issues with CASE !!!!!
the query which i proposed to you has no performance isuue

See attached.
query.txt