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 th…

Accepted Answer

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;
```

MySQL selecting rows with a max id and matching other conditions

Solution

Problem