1

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.

  • Unless all columns in select (except backups.id) is functionally dependent of backups.resource_id you will get a random result from your query. – Lennart Aug 14 '16 at 15:53
1

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
-1

harddrives.id As id1

Update it to

(Max)harddrives.id As id1

I think that should work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.