• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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

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
chaseivey
Asked:
chaseivey
  • 8
  • 8
  • 3
  • +6
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
select *
from tableName
where brandID=0, brandName='other'
order by brandName
0
 
gplanaCommented:
You should do something like this:

SELECT *
FROM your_table
ORDER BY CASE brandID WHEN brandID=0 THEN 'A' ELSE 'B' END, brandName
0
 
dportasCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
gplanaCommented:
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
 
chinawalCommented:
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
 
Reza RadConsultant, TrainerCommented:
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
 
gplanaCommented:
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
 
chaseiveyAuthor Commented:
Which of these solutions would perform the fastest?
0
 
gplanaCommented:
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
 
chaseiveyAuthor Commented:
So, regardless, I will have to use 2 SELECT statements?
0
 
shru_0409Commented:
SELECT *
FROM yr_tbl
ORDER BY CASE WHEN brandID=0  and brandName = 'óther' THEN 'Z' ELSE 'A' END

try this...


0
 
gplanaCommented:
Yes, if you use 2 SELECTs, the performance will be worst than using just one SELECT.
Which database system are you using ?
0
 
shru_0409Commented:
no proble u can use 2 select statement ..... but what it is????
0
 
dportasCommented:
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
 
gplanaCommented:
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
 
dportasCommented:
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
 
chaseiveyAuthor Commented:
I am using mySQL
0
 
gplanaCommented:
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
 
chaseiveyAuthor Commented:
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
 
chaseiveyAuthor Commented:
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
 
Reza RadConsultant, TrainerCommented:
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
 
chaseiveyAuthor Commented:
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
 
SharathData EngineerCommented:
Can you post your sample data and how you want t o display?
0
 
chaseiveyAuthor Commented:
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
 
SharathData EngineerCommented:
did you try this?

select brandName from YourTable order by brandID desc
0
 
chaseiveyAuthor Commented:
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
 
Reza RadConsultant, TrainerCommented:
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
 
SharathData EngineerCommented:
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
 
gplanaCommented:
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
 
awking00Commented:
See attached.
query.txt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 8
  • 8
  • 3
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now