Mysql – Select highest id of each type in thesql

join;MySQLsubquery

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ids. My Query is below.

Select
  backups.id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  backups.id

This outputs the following:

enter image description here
The latest backup for SDSBackup_OFFICE should have an ID of 118. I have read about using either subqueries or different types of joins but I wasn't able to get them to work. Any ideas?

I tried the following query as one of the answers below suggested without success. The max backup.id was indeed selected, but it was incorrectly matched up with other values in the database.

Select
  Max(backups.id) As Max_id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  Max_id

I did some research and found that the groupwise max function only applies to the given column that it is applied to. It is left to the server to determine how the other data should be assigned. I don't know if I fully understand this. I am getting my information from a question/answer on stackoverflow. https://stackoverflow.com/questions/7594465/group-wise-maximum-of-a-certain-column.

All the examples on that page are fairly simple queries, I don't know how to apply these examples to my query. Help would be much appreciated.

Best Answer

I think you're looking at something like this:

Select A.Max_id, 
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM  
  ( Select  backups.resource_id,
    Max(backups.id) As Max_id
      FROM backups  Group by backups.resource_id 
      Order By backups.start_time Desc ) A 
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id

OR (order by outside, don't remember)

Select A.Max_id, 
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM 
  (  Select  backups.resource_id,
    Max(backups.id) As Max_id
  FROM backups  Group by backups.resource_id ) as A 
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Order By   A.start_time Desc
Related Question