CODEWITHSUNDEEP
mysqlsqlfilteringdistinct
user2761702
user2761702

Reputation: 13

Order by multiple conditions

im very noob and this became ungoogleable (is that a word?)

the rank is by time but..

time done with ( A=0 ) AND ( B=0 ) beat everyone
time done with ( A=0 ) AND ( B=1 ) beat everyone with ( A=1 )
time done with ( A=1 ) AND ( B=0 ) beat everyone with ( A=1 + B=1 )

rank example (track=desert)

pos--car------time---A----B
1.---yellow----90----No---No
2.---red-------95----No---No
3.---grey-----78-----No---Yes
4.---orange--253---No---Yes
5.---black----86----Yes---No
6.---white----149---Yes---No
7.---pink-----59----Yes---Yes
8.---blue-----61----Yes---Yes

to make it even worst, the table accept multiple records for the same car

here is the entries

create table `rank`
(
`id` int not null auto_increment,
`track` varchar(25) not null,
`car` varchar(32) not null,
`time` int not null,
`a` boolean not null,
`b` boolean not null,
primary key (`id`)
);
insert into rank (track,car,time,a,b) values
 ('desert','red','95','0','0'),
 ('desert','yellow','89','0','1'),
 ('desert','yellow','108','0','0'),
 ('desert','red','57','1','1'),
 ('desert','orange','120','1','0'),
 ('desert','grey','85','0','1'),
 ('desert','grey','64','1','0'),
 ('desert','yellow','90','0','0'),
 ('desert','white','92','1','1'),
 ('desert','orange','253','0','1'),
 ('desert','black','86','1','0'),
 ('desert','yellow','94','0','1'),
 ('desert','white','149','1','0'),
 ('desert','pink','59','1','1'),
 ('desert','grey','78','0','1'),
 ('desert','blue','61','1','1'),
 ('desert','pink','73','1','1');

please, help? :p

ps: sorry about the example table

Upvotes: 1

Views: 99

Answers (1)

Andomar
Andomar

Reputation: 238096

To prioritize a, then b, then time, use order by b, a, time.

You can use a not exists subquery to select only the best row per car.

Finally, you can add a Pos column using MySQL's variables, like @rn := @rn + 1.

Example query:

select  @rn := @rn + 1 as pos
,       r.*
from    rank r
join    (select @rn := 0) init
where   not exists
        (
        select  *
        from    rank r2
        where   r.car = r2.car
                and (
                    r2.a < r.a
                    or (r2.a = r.a and r2.b < r.b)
                    or (r2.a = r.a and r2.b = r.b and r2.time < r.time)
                )
        )
order by
        b
,       a
,       time

See it working at SQL Fiddle.

Upvotes: 1

Related Questions