SQL Server- ORDER BY CASE

9 thoughts on “SQL Server- ORDER BY CASE”

1. user November 30, -0001 at 12:00 am

   The query looks fine to me, does the query run in SqlServer Management Studio?

   Reply ↓
2. user November 30, -0001 at 12:00 am

   Do you have ImageName1 column in your table?

   Reply ↓
3. user November 30, -0001 at 12:00 am

   ImageName1 is in the table, and like the answers below state it does run fine outside of its dynamic form.

   Reply ↓
4. user November 30, -0001 at 12:00 am

   Try enclosing the case statement in parentheses.

   SELECT *
   FROM Vehicles
   WHERE (DKID IN (69954))
   ORDER BY 
       (case when ImageName1 = 'na' then 0 else 1 end), Make , Model, Year DESC
   

   Reply ↓
5. user November 30, -0001 at 12:00 am

   works for me

   here is repo script

   use tempdb
   go
   
   create table Vehicles(DKID int,ImageName1 varchar(50),
                             Make int, Model int, Year int)
   
   insert Vehicles values (69954,'na',1,1,2007)
   insert Vehicles values(69954,'bla',1,1,2008)
   go
   
   SELECT *
   FROM Vehicles
   WHERE (DKID IN (69954))
   ORDER BY case when ImageName1 = 'na' then 0 else 1 end, 
   Make , Model, Year DESC
   

   Reply ↓
6. user November 30, -0001 at 12:00 am

   are you running this query dynamically?, if so you might need to escape the quotes around ‘na’:

   SELECT *
   FROM Vehicles
   WHERE (DKID IN (69954))
   ORDER BY case when ImageName1 = ''na'' then 0 else 1 end, Make , Model, Year DESC
   

   Reply ↓
7. user November 30, -0001 at 12:00 am

   Your query works fine for me in SQL Mgmt Studio… Maybe try it this way instead to see if it gets you anywhere:

   SELECT
       case when ImageName1 = 'na' then 0 else 1 end as OrderCol,
       *
   FROM Vehicles
   WHERE (DKID IN (69954))
   ORDER BY OrderCol,Make,Model,Year DESC
   

   Reply ↓
8. user November 30, -0001 at 12:00 am

   You’re using JDBC. Is there probably a transformation / interpretation from JDBC? Try making the ‘na’ a parameter. Check if there is a certain syntax in JDBC for string constants in queries. I don’t use JDBC, so I could be completely wrong.

   Reply ↓
9. user November 30, -0001 at 12:00 am

   As KMike said, it looks like you didn’t not escape properly.

   Basically, when you ran your statement, it did not generate a syntactically correct SQL statement from the dynamic SQL.

   Generally, when I am writing dynamic sql, I use a print statement to print the generated SQL. I can then review the generated sql visually for obvious mistakes, and then execute it it to make sure it works as expected.

   If I made a mistake in the dynamic SQL, it will usually be revealed here.

   Reply ↓