[SOLVED] MySQL: Get related record with largest value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

[SOLVED] MySQL: Get related record with largest value

Post by batfastad »

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:

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`
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
Last edited by batfastad on Fri Apr 30, 2010 10:59 am, edited 1 time in total.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL: Get related record with largest value

Post by mikosiko »

if in addition to the fields that you are selecting from the companies table you want to show ONLY the note field from the notes table you can use a scalar sub-query... like

Code: Select all

SELECT `fields-from-companies-table`, (SELECT `note` FROM `notes` WHERE `notes`.`company_id` = `companies`.`company_id` ORDER BY `create_stamp` DESC LIMIT 0, 1)
FROM `companies` 
WHERE `companies`.`rep` LIKE "%MBC%"
ORDER BY `companies`.`company_name`
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL: Get related record with largest value

Post by Eran »

It's hard to tell what's really going on with that query since the formatting is kind of messed up. I wrote about this issue a while ago on my blog, you are welcome to go over it - http://www.techfounder.net/2010/03/12/f ... ith-mysql/
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL: Get related record with largest value

Post by batfastad »

Yeah strange on the formatting, I just typed the query up in Notepad++ and never seen that happen before

@mikosiko
Yeah unfortunately I do need to get other fields from the notes table

@pytrin
That article was excellent. Just what I needed, I now have a much better understanding of derived tables for this sort of query.

Great stuff! :lol:
Thanks for the help both of you

Cheers, B
Post Reply