MySQL selecting rows with a max id and matching other conditions

Question

Using the tables below as an example and the listed query as a base query, I want to add a way to select only rows with a max id! Without having to do a second query!

TABLE VEHICLES

id      vehicleName
-----   --------
1       cool car
2       cool car
3       cool bus
4       cool bus
5       cool bus
6       car
7       truck
8       motorcycle
9       scooter
10      scooter
11      bus

TABLE VEHICLE NAMES

nameId  vehicleName
------  -------
1       cool car
2       cool bus
3       car
4       truck
5       motorcycle
6       scooter
7       bus

TABLE VEHICLE ATTRIBUTES

nameId  attribute
------  ---------
1       FAST
1       SMALL
1       SHINY
2       BIG
2       SLOW
3       EXPENSIVE
4       SHINY
5       FAST
5       SMALL
6       SHINY
6       SMALL
7       SMALL

And the base query:

select a.*
  from vehicle         a
  join vehicle_names   b using(vehicleName)
  join vehicle_attribs c using(nameId)
 where c.attribute in('SMALL', 'SHINY')
 and a.vehicleName like '%coo%'
 group 
    by a.id
having count(distinct c.attribute) = 2;

So what I want to achieve is to select rows with certain attributes, that match a name but only one entry for each name that matches where the id is the highest!

So a working solution in this example would return the below rows:

id      vehicleName
-----   --------
2       cool car
10      scooter

if it was using some sort of max on the id

at the moment I get all the entries for cool car and scooter.

My real world database follows a similar structure and has 10's of thousands of entries in it so a query like above could easily return 3000+ results. I limit the results to 100 rows to keep execution time low as the results are used in a search on my site. The reason I have repeats of "vehicles" with the same name but only a different ID is that new models are constantly added but I keep the older one around for those that want to dig them up! But on a search by car name I don't want to return the older cards just the newest one which is the one with the highest ID!

The correct answer would adapt the query I provided above that I'm currently using and have it only return rows where the name matches but has the highest id!

If this isn't possible, suggestions on how I can achieve what I want without massively increasing the execution time of a search would be appreciated!

Answer 1

If you want to keep your logic, here what I would do:

select a.*
from vehicle a
    left join vehicle a2 on (a.vehicleName = a2.vehicleName and a.id < a2.id)
    join vehicle_names   b on (a.vehicleName = b.vehicleName)
    join vehicle_attribs c using(nameId)
where c.attribute in('SMALL', 'SHINY')
    and a.vehicleName like '%coo%'
    and a2.id is null
group by a.id
having count(distinct c.attribute) = 2;

Which yield:

+----+-------------+
| id | vehicleName |
+----+-------------+
|  2 | cool car    |
| 10 | scooter     |
+----+-------------+
2 rows in set (0.00 sec)

As other said, normalization could be done on few levels:

Keeping your current vehicle_names table as the primary lookup table, I would change:

update vehicle a
    inner join vehicle_names b using (vehicleName)
set a.vehicleName = b.nameId;
alter table vehicle change column vehicleName nameId int;

create table attribs (
    attribId int auto_increment primary key,
    attribute varchar(20),
    unique key attribute (attribute)
);
insert into attribs (attribute)
    select distinct attribute from vehicle_attribs;
update vehicle_attribs a
    inner join attribs b using (attribute)
set a.attribute=b.attribId;
alter table vehicle_attribs change column attribute attribId int;

Which led to the following query:

select a.id, b.vehicleName
from vehicle a
    left join vehicle a2 on (a.nameId = a2.nameId and a.id < a2.id)
    join vehicle_names b on (a.nameId = b.nameId)
    join vehicle_attribs c on (a.nameId=c.nameId)
    inner join attribs d using (attribId)
where d.attribute in ('SMALL', 'SHINY')
    and b.vehicleName like '%coo%'
    and a2.id is null
group by a.id
having count(distinct d.attribute) = 2;