MAX() not giving me the entire row

[kthorpe]

I'm working on a timesheet tracking application - each time a person's hours are changed, a new row gets added to the database. The columns are timeid (int primary key), employmentid (int), hours (decimal 4,2) and payperiodid (int).

For each employment, I want to query the most recent hours for each payperiod.

SELECT hours,ppid,max(timeid) FROM hours WHERE employmentid=%d GROUP BY ppid

This query seems like it should do what I want - and in all the tutorials / examples I've read, it does.

But when *I* run it, it returns a table of data: a column of max(timeid)s which correspond to their respective payperiodids. But the hours are NOT correct - this column is being queried without being tied to the max(timeid) result.

A coworker suggested I run a query inside a query, but this is way too inefficient for my needs, and seems unnecessary.

Why isnt my query working properly!!!!!?? ???

[code green]

This is actually an illegal query in SQL server and others but MySQL lets you get away with it.
However, that doesn't mean it always returns the expected data.
Although I can't see why it doesn't this time.

This is quite an involved issue - but how does the engine know WHICH group of hours to return?

You GROUP BY ppid, which will return unique ppids.
You specify the timeid to return by MAX(timeid).

But which hours for same the ppid?
MySQL usually resolves this but it is incorrect syntax

Try GROUP BY ppid, hours