1005264 - Please check my query

Status: RESOLVED INVALID

(Data & BI Services Team :: DB: MySQL, task)

Description

[Mike Hommey [:glandium]]

As per irc. This is a query on the buildbot database. My original query was:
select distinct builds.id as build_id, builders.name as builder_name, UNIX_TIMESTAMP(builds.starttime) as build_starttime, UNIX_TIMESTAMP(builds.endtime) as build_endtime, slaves.name as slave_name, steps.id as step_id, steps.name as step_name, steps.description as step_description, steps.order as step_order, UNIX_TIMESTAMP(steps.starttime) as step_starttime, UNIX_TIMESTAMP(steps.endtime) as step_endtime, status as step_status from steps, builds, builders, slaves where steps.build_id = builds.id AND builds.builder_id = builders.id AND builds.slave_id = slaves.id and builds.result = 0 and builders.id in ($ids) order by builds.starttime asc, builds.id asc, steps.order asc

where $ids comes from the shell, and is a set of numbers corresponding to what i'm interested in, in chunks.

My goal was to add UNIX_TIMESTAMP(changes.when) to the output for each build, the problem being that in the database, there can be several changes corresponding to a given build. For instance, the following returns 10 items:

select changes.when from builds, source_changes, changes, builders where builds.builder_id = builders.id and builds.source_id = source_changes.source_id and source_changes.change_id = changes.id and builds.id = 29334668

But really, I only want the last one of those. So the query I could come up with is:

select distinct builds.id as build_id, builders.name as builder_name, UNIX_TIMESTAMP(MAX(changes.when)) as push_time, UNIX_TIMESTAMP(builds.starttime) as build_starttime, UNIX_TIMESTAMP(builds.endtime) as build_endtime, slaves.name as slave_name, steps.id as step_id, steps.name as step_name, steps.description as step_description, steps.order as step_order, UNIX_TIMESTAMP(steps.starttime) as step_starttime, UNIX_TIMESTAMP(steps.endtime) as step_endtime, status as step_status from steps, builds, builders, slaves, source_changes, changes where steps.build_id = builds.id AND builds.builder_id = builders.id AND builds.slave_id = slaves.id and builds.result = 0 and builders.id in ($ids) and builds.source_id = source_changes.source_id and source_changes.change_id = changes.id group by steps.id order by builds.starttime asc, builds.id asc, steps.order asc

and it /seems/ to work.

Comment 1

[Mike Hommey [:glandium]]

Actually, forget it. The info is unfortunately not useful on some builds, so i have to correlate with data that's not in the db :(

Comment 2

[Mike Hommey [:glandium]]

Sorry for the bother if you started looking.