[SOLVED] MySQL: Get related record with largest value
Posted: Fri Apr 30, 2010 6:20 am
Hi everyone
I have 2 tables: companies and notes
They're related by a company_id field
Each company record has many related records in the notes table
The notes table has 3 fields: company_id, create_stamp, note.
The create_stamp is a unix timestamp stored as an unsigned integer (not MySQL timestamp).
I'm trying to list a bunch of records from the companies table but I'd also like to output the most recent note for each company.
Since I'm using unix timestamps the most recent related note is the one with the largest value of create_stamp
However I also want to sort the results by the create_stamp returned from the join in ascending order.
I hope I've explained that ok, here's what I've got so far:
I don't know much about sub-queries or joining on derived tables like this but the query seems to run without any errors.
The query above returns the correct number of results from the companies table as specified by the where clause... WHERE `companies`.`rep` LIKE '%MBC%'
The problem with the query above is that it's not returning the columns from notes_join that I wanted... so I've almost certainly borked up the join query
Anyone see anything obvious I'm doing wrong here?
Or any other comments/suggestions?
Cheers, B
I have 2 tables: companies and notes
They're related by a company_id field
Each company record has many related records in the notes table
The notes table has 3 fields: company_id, create_stamp, note.
The create_stamp is a unix timestamp stored as an unsigned integer (not MySQL timestamp).
I'm trying to list a bunch of records from the companies table but I'd also like to output the most recent note for each company.
Since I'm using unix timestamps the most recent related note is the one with the largest value of create_stamp
However I also want to sort the results by the create_stamp returned from the join in ascending order.
I hope I've explained that ok, here's what I've got so far:
Code: Select all
SELECT `companies`.`rep`, FROM_UNIXTIME(`notes_join`.`create_stamp`, '%e/%m/%y'), `notes_join`.`note`, `companies`.`company_id`, `companies`.`company_name`
FROM `companies`
LEFT JOIN (
SELECT `company_id`, `create_stamp`, `note` FROM `notes` ORDER BY `create_stamp` DESC LIMIT 0, 1
) AS `notes_join` USING(`company_id`)
WHERE `companies`.`rep` LIKE '%MBC%'
ORDER BY `notes_join`.`create_stamp`, `companies`.`company_name`The query above returns the correct number of results from the companies table as specified by the where clause... WHERE `companies`.`rep` LIKE '%MBC%'
The problem with the query above is that it's not returning the columns from notes_join that I wanted... so I've almost certainly borked up the join query
Anyone see anything obvious I'm doing wrong here?
Or any other comments/suggestions?
Cheers, B