CODEWITHSUNDEEP
mysqlsymfonydoctrine-orm
Павел Кушнеревич
Павел Кушнеревич

Reputation: 180

Putting order according to the condition

I have a query:

SELECT * FROM Cars WHERE (cars.mark = "Audi" and cars.model = "a4" and cars.year = 2008) or 
             (cars.mark = "Audi" and cars.model <> "a4" and cars.year = 2008) or  
             (cars.mark <> "Audi" and cars.model <> "a4" and cars.year = 2008 )

How can I order it by condition. The first results: cars.mark = "Audi" and cars.model = "a4" and cars.year = 2008 next result: (cars.mark = "Audi" and cars.model <> "a4" and cars.year = 2008) and the third : (cars.mark <> "Audi" and cars.model <> "a4" and cars.year = 2008 ).

I cannot using union query, because it is Symfony project and it doesn't support union. Thanks

Upvotes: 0

Views: 29

Answers (1)

P.Salmon
P.Salmon

Reputation: 17665

Not sure that your where condition is useful basically it says give me all marks an models for 2008, I'm pretty sure that model is unique across all marks. As for ordering of the results you can conditionally order.

drop table if exists t;
create table t
(mark varchar(4) , model varchar(4), year int);

insert into t values
('audi','a3',2008),
('ford','st',2008),
('audi','a4',2008),
('gm','junk',2008),
('gm','junk',2009),
('ford','xx',2008);


SELECT * FROM t 
WHERE  year = 2008
order by 
case when t.mark = "Audi" and t.model = "a4"  then 1 
     when t.mark = "Audi" and t.model <> "a4"  then 2 
     when t.mark <> "Audi" and t.model <> "a4" then 3
     else 4
     end ,
mark desc;

+------+-------+------+
| mark | model | year |
+------+-------+------+
| audi | a4    | 2008 |
| audi | a3    | 2008 |
| gm   | junk  | 2008 |
| ford | st    | 2008 |
| ford | xx    | 2008 |
+------+-------+------+

Upvotes: 1

Related Questions