can I select max(id) using WHERE?

[stevieontario]

Morning PHPFreaks, this might be an easy problem to solve but I'm stumped.

 

I have two tables:

Source (id, filename, filedatetime)

Perform (performid, sourceid, machinename, machinetype, output)

 

Every hour one new row will go into Source (source.id is an auto-incremented primary key), and 100 new rows will go into Perform. The 100 new rows that go into Perform will each have the same sourceid (which is of course the same number as source.id).

 

My query: I want to select perform.output for each hour, and group the results by machinetype, and order the results by output. Then I can look at output trends for specified time periods.

 

My problem: I want to query perform for output in the most recent hour, i.e., to retrieve the most recent id. So far the only way I have been able to do that is like so:

SELECT machinetype AS "Machine type", format( sum( output ) , 0 ) AS "Machine output", 
FROM perform
WHERE sourceid =790
GROUP BY machinetype
ORDER BY sum( output ) DESC

 

This of course requires that I know that the last sourceid is indeed 790.

 

My question: how can I achieve the same result without having to learn the specific sourceid?

 

Thanks in advance!

 

 

[Mchl]

SELECT machinetype AS "Machine type", format( sum( output ) , 0 ) AS "Machine output",
FROM perform
CROSS JOIN
(SELECT MAX(id) AS sourceid FROM Source) AS sq
USING (sourceid)
GROUP BY machinetype
ORDER BY sum( output ) DESC

[stevieontario]

thanks Mchl,

 

Unfortunately I got this message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM perform CROSS JOIN (SELECT MAX(id) AS sourceid FROM Source) AS sq U' at line 2

[Mchl]

Remove the comma (,) just before FROM

[stevieontario]

ahh... beautiful! thanks so much, this was driving me nuts