Complicated MySQL Database Query

Question

I have the following database structure:

Sites table

id  |  name  |  other_fields

Backups table

id  |  site_id  | initiated_on(unix timestamp) | size(float) | status

So Backups table have a Many to One relationship with Sites table connected via site_id

And I would like to output the data in the following format

name | Latest initiated_on | status of the latest initiated_on row

And I have the following SQL query

SELECT *, `sites`.`id` as sid, SUM(`backups`.`size`) AS size
FROM (`sites`)
LEFT JOIN `backups` ON `sites`.`id` = `backups`.`site_id`
WHERE `sites`.`id` =  '1'
GROUP BY `sites`.`id`
ORDER BY `backups`.`initiated_on` desc

The thing is, with the above query I can achieve what I am looking for, but the only problem is I don't get the latest initiated_on values.

So if I had 3 rows in backups with site_id=1, the query does not pick out the row with the highest value in initiated_on. It just picks out any row.

Please help, and

thanks in advance.

You shouldn't write SELECT * when you have a GROUP BY - the values that you'll get back will be arbitrary. In most DBs this wouldn't even be a valid SQL statement. (And even when you aren't using GRUOP BY, SELECT * is still considered to be a bad practice). — Mark Byers
– Mark Byers, Commented Feb 10, 2012 at 14:55
You haven't added initiated_on to your SELECT which is probably why it's not returning it. — CBusBus
– CBusBus, Commented Feb 10, 2012 at 15:26
you may use max(initiated_on) to get the maximum on a group by — mdprotacio
– mdprotacio, Commented Feb 10, 2012 at 15:27

piotrm · Accepted Answer · 2012-02-10 16:44:11Z

2

You should try:

SELECT sites.name, FROM_UNIXTIME(b.latest) as latest, b.size, b.status
FROM sites
LEFT JOIN
  ( SELECT bg.site_id, bg.latest, bg.sizesum AS size, bu.status
    FROM
      ( SELECT site_id, MAX(initiated_on) as latest, SUM(size) as sizesum
        FROM backups
        GROUP BY site_id ) bg
    JOIN backups bu
    ON bu.initiated_on = bg.latest AND bu.site_id = bg.site_id
  ) b
ON sites.id = b.site_id

In the GROUP BY subquery - bg here, the only columns you can use for SELECT are columns that are either aggregated by a function or listed in the GROUP BY part.

http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html
Once you have all the aggregate values you need to join the result again to backups to find other values for the row with latest timestamp - b.
Finally join the result to the sites table to get names - or left join if you want to list all sites, even without a backup.

edited Feb 10, 2012 at 16:44

answered Feb 10, 2012 at 15:28

piotrm

12.4k5 gold badges33 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1191247 Over a year ago

This is the most efficient solution but you should also add "bu.site_id = bg.site_id" to the innermost join

piotrm Over a year ago

Yes you are right, I assumed int timestamp was unique enough, edited.

ekhaled Over a year ago

Thank you, well explained and the query is efficient in my crude benchmarking.

Aleksandar Vucetic · Accepted Answer · 2012-02-10 15:24:24Z

1

Try with this:

select S.name, B.initiated_on, B.status
from sites as S left join backups as B on S.id = B.site_id
where B.initiated_on = 
       (select max(initiated_on)
           from backups
          where site_id = S.id)

answered Feb 10, 2012 at 15:24

Aleksandar Vucetic

15k9 gold badges56 silver badges56 bronze badges

Comments

Matt Gibson · Accepted Answer · 2012-02-10 17:40:40Z

1

To get the latest time, you need to make a subquery like this:

    SELECT sites.id as sid, 
           SUM(backups.size) AS size
           latest.time AS latesttime
      FROM sites AS sites
 LEFT JOIN (SELECT site_id, 
                   MAX(initiated_on) AS time
              FROM backups
          GROUP BY site_id) AS latest
        ON latest.site_id = sites.id
 LEFT JOIN backups 
        ON sites.id = backups.site_id
     WHERE sites.id =  1
  GROUP BY sites.id
  ORDER BY backups.initiated_on desc

I have removed the SELECT * as this will only work using MySQL and is generally bad practice anyway. Non-MySQL RDBSs will throw an error if you include the other fields, even individually and you will need to make this query itself into a subquery and then do an INNER JOIN to the sites table to get the rest of the fields. This is because they will be trying to add all of them into the GROUP BY statement and this fails (or is at least very slow) if you have long text fields.

edited Feb 10, 2012 at 17:40

answered Feb 10, 2012 at 15:34

Matt Gibson

15k8 gold badges51 silver badges80 bronze badges

1 Comment

Matt Gibson Over a year ago

Oops! That was what I had in mind. Edited to use the right column name.

Collectives™ on Stack Overflow

Complicated MySQL Database Query

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related