MySQL selects rows with maximum id and matches other conditions

Question

Using the following tables as an example and the specified query as a basic query, I want to add a way to select only rows with maximum id! Without the need to fulfill the second request!

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 basic request:

 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, I want to select rows with specific attributes that match the name, but only one entry for each name that matches where the identifier is the highest!

So, the working solution in this example will return the following lines:

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

if he used some kind of max on id

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

My database in the real world follows a similar structure and has 10 thousand records in it, so such a query, as above, can easily return 3000+ results. I limit the results to 100 lines to reduce lead time, as the results are used when searching on my site. The reason I have repetitions of “vehicles” with the same name but only with a different identifier is because new models are constantly being added, but I keep the older one for those who want to dig them out! But when searching by car name, I don’t want to return old cards only the newest ones that have the highest identifier!

The correct answer will be to adapt the above query, which I am currently using, and return to it only the lines in which the name matches, but has the highest identifier!

If this is not possible, suggestions on how I can achieve what I want without massively increasing the search execution time will be evaluated.

Answer 1

If you want to keep your logic, here is what I will 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; 

What result:

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

Like all of the above, normalization can be performed at several levels:

By keeping the current vehicle_names table as the main 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; 

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

Answer 2

The table does not seem normalized, however this will make it easier for you:

 select max(id), vehicleName from VEHICLES group by vehicleName having count(*)>=2; 

Answer 3

I'm not sure I fully understand your model, but the following request satisfies your requirements. The first subquery finds the latest version of the car. The second query satisfies your "and" condition. Then I just join the queries for the vehicle name (what is the key?).

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

If this logic of the “newest car” is what you will need to do a lot, a small suggestion is to create a view (see below) in which the latest version of each vehicle will be displayed. You can then use the view instead of the find-max query. Please note that this is just for ease of use, it does not offer performance benefits.

 select * from vehicle a where id = (select max(b.id) from vehicle b where a.vehiclename = b.vehiclename); 

Answer 4

Without going into the right model redesign, you could

1) Add an IsLatest column that can control your application.

This is not ideal, but it will satisfy you with a question (until the next problem, see not at the end) All you need is when you add a new record to issue queries such as

 UPDATE a SET IsLatest = 0 WHERE IsLatest = 1 INSERT new a UPDATE a SET IsLatest = 1 WHERE nameId = @last_inserted_id 

in a transaction or trigger

2) Alternatively, you can find out max_id before executing your request

 SELECT MAX(nameId) FROM a WHERE vehicleName = @name 

3) You can do this in one SQL and provide indexes (vehicle name, name), it should have decent speed with

 select a.* from vehicle a join vehicle_names b ON a.vehicleName = b.vehicleName join vehicle_attribs c ON b.nameId = c.nameId AND c.attribute = 'SMALL' join vehicle_attribs d ON b.nameId = c.nameId AND d.attribute = 'SHINY' join vehicle notmax ON a.vehicleName = b.vehicleName AND a.nameid < notmax.nameid where a.vehicleName like '%coo%' AND notmax.id IS NULL 

I removed GROUP BY and HAVING and replaced it with another join (assuming that only one attribute for the name is possible).

I also used one of the ways to find max for each group, namely to join the table and filter the row for which there are no records with a large identifier for the same name.

There are other search methods: "max per group sql". Also see here , although not complete.