?
Solved

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

Posted on 2010-01-03
30
Medium Priority
?
265 Views
Last Modified: 2012-05-08
Hello,

I have a list of numeric 'brandID's that include this row:
 brandID=0, brandName='other'

I want a simple SELECT statement that will 'ORDER BY brandName' but will tack the above row at the end, rather than at it's alphabetic placement.  What is the syntax for this?
0
Comment
Question by:chaseivey
  • 8
  • 8
  • 3
  • +6
30 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26166302
select *
from tableName
where brandID=0, brandName='other'
order by brandName
0
 
LVL 15

Expert Comment

by:gplana
ID: 26166319
You should do something like this:

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

Expert Comment

by:dportas
ID: 26166320
SELECT brandID, brandName
FROM
(
 SELECT CASE WHEN brandID = 0 THEN 1 ELSE 0 END ord,
 brandID, brandName
 FROM Brand
) b
ORDER BY ord, brandName ;
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 15

Expert Comment

by:gplana
ID: 26166334
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.
0
 
LVL 5

Expert Comment

by:chinawal
ID: 26166337
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
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26166338
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
0
 
LVL 15

Expert Comment

by:gplana
ID: 26166341
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.
0
 

Author Comment

by:chaseivey
ID: 26166347
Which of these solutions would perform the fastest?
0
 
LVL 15

Expert Comment

by:gplana
ID: 26166380
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
0
 

Author Comment

by:chaseivey
ID: 26166384
So, regardless, I will have to use 2 SELECT statements?
0
 
LVL 14

Expert Comment

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

try this...


0
 
LVL 15

Expert Comment

by:gplana
ID: 26166392
Yes, if you use 2 SELECTs, the performance will be worst than using just one SELECT.
Which database system are you using ?
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26166399
no proble u can use 2 select statement ..... but what it is????
0
 
LVL 22

Expert Comment

by:dportas
ID: 26166415
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.
0
 
LVL 15

Expert Comment

by:gplana
ID: 26166429
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.
0
 
LVL 22

Expert Comment

by:dportas
ID: 26166441
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.
0
 

Author Comment

by:chaseivey
ID: 26166491
I am using mySQL
0
 
LVL 15

Expert Comment

by:gplana
ID: 26167243
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.
0
 

Author Comment

by:chaseivey
ID: 26168607
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.  
0
 

Author Comment

by:chaseivey
ID: 26168608
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.  
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26168634
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

Open in new window

0
 

Author Comment

by:chaseivey
ID: 26168652
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?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26168676
Can you post your sample data and how you want t o display?
0
 

Author Comment

by:chaseivey
ID: 26168691
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)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26168695
did you try this?

select brandName from YourTable order by brandID desc
0
 

Author Comment

by:chaseivey
ID: 26168698
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) :)
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26168700
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
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 100 total points
ID: 26168723
then gplana is correct. You can try like this.


select brandname from YourTable order by case when brandName = 'OTHER' then 2 else 1 end case,brandname

you can also try with ELT function like this.

select brandName from YourTable order by ELT(brandName <=> 'OTHER', 1,0), brandName
0
 
LVL 15

Accepted Solution

by:
gplana earned 400 total points
ID: 26169152
Sorry for my silence. I were sleeping.
CASE doesn't have a performance impact as it just spend a very little of CPU on your mysql server. Sharath_123 is right> Put a 2 inside the THEN instead of 0. My solution puts OTHER at the beggining instead to the end. Sorry.
Regards.
0
 
LVL 32

Expert Comment

by:awking00
ID: 26172177
See attached.
query.txt
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question