CODEWITHSUNDEEP
mysql
zb'
zb'

Reputation: 8059

select max() and other fields

We have a table:

create table ducks (id int(8) primary key not null auto_increment,
                    name varchar(255),
                    car varchar(255), 
                    money int(8)
                   );
insert into ducks set name='donald', car='none', money=10;
insert into ducks set name='scrudge', car='bip', money=10000;
insert into ducks set name='mac', car='bip', money=1000;
insert into ducks set name='joe', car='boo', money=2000000;

So analizing this I found that we got slow queries on the following request:

select name,money from ducks where car='bip' order by money DESC LIMIT 1;

because table is big and sorting just to get one record is very long

I found that the following works faster:

select distinct name,money from ducks where money=(select max(money) from ducks where car='bip')  LIMIT 1;

but still not sure, because it is subselects.

what is common way to solve this ?

http://sqlfiddle.com/#!2/d2b7ed/6

update it turned that in reality we have a task to not searching same car but search richest duck which have less than $100000

http://sqlfiddle.com/#!2/d2b7ed/21

Upvotes: 1

Views: 211

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169494

You are wise to be wary of subqueries; especially in MySQL.

The following query uses a self-exclusion join, and in basic testing performs the best of the three. Your first solution is fine, but as you say slow. It is also not ANSI-compliant, but that may not matter to you. Your second solution is also fine, but MySQL does not handle subqueries as well as might be hoped; at least traditionally.

select 
  d.name, d.money
from 
  ducks d
  left join ducks d2 
  on d2.car = d.car 
  and d2.money > d.money
where 
  d.car = 'bip'
  and d2.id is null

Implemented here: http://sqlfiddle.com/#!2/27711/20

Edit: The goalposts somehow moved. Darn those goalposts. This is the self-exclusion join solution for the new problem: http://sqlfiddle.com/#!2/7146d/13

select 
  d.name, d.money 
from 
  ducks d
  left join ducks d2 
  on d2.money > d.money
  and d2.money < 100000
where 
  d.money < 100000 
  and d2.id is null;

Upvotes: 1

Related Questions