question

I was about to say "The SELECT always comes first" when I realized that you are using the query to populate the ELSE portion of your case statement. I think that, in general, having subqueries to populate a single column directly isn't a good practice. In your case, I would prefer to do a JOIN to the subquery (using max(VehicleCC) rather than top(1) order by VehicleCC DESC) so the query only executes once rather than running once for each row in the result set. Try this: SELECT CASE WHEN CAST(VehicleCC AS VARCHAR(50)) BETWEEN -1 AND 50 THEN 'B1' WHEN CAST(VehicleCC AS VARCHAR(50)) BETWEEN 51 AND 125 THEN 'B2' WHEN CAST(VehicleCC AS VARCHAR(50)) BETWEEN 126 AND 500 THEN 'B3' WHEN CAST(VehicleCC AS VARCHAR(50)) BETWEEN 501 AND 650 THEN 'B4' WHEN CAST(VehicleCC AS VARCHAR(50)) BETWEEN 651 AND 1000 THEN 'B5' WHEN CAST(VehicleCC AS VARCHAR(50)) > 1001 THEN 'B6' ELSE defaultVehicle.VehicleCC END AS VehicleCC /* ... your other columns ... */ FROM vehicle_table AS cl LEFT JOIN ( SELECT MAX(veh.VehicleCC) , HIRP.FKBLDRefId FROM HIRE_INSTRUCTION AS HIRP INNER JOIN VEHICLE AS VEH ON HIRP.FKFleetVehicleID = VEH.VehicleID WHERE HIRP.Deleted = 0 AND VEH.Deleted = 0 GROUP BY HIRP.FKBLDRefID ) AS defaultVehicle ON cl.FKBLDRefID = defaultVehicle.FDBLDRefID