Following are the two tables i have:
i need to write a mysql statement that will do followings:
- list the latest updated record of each company i.e. single record per company
- it should only list todays/current date record
interms of sorting:
- highest stock value with latest updated should be displayed on top.
- if both company has same stock value then the one who updated first should go on top. (i.e. latest updated should be on second)
i did something like this but it displays multiple records for each company:
SELECT * from tblcompany, tblstock
WHERE tblcompany.comid = tblstock.comid
AND tblstock.updated >= CURRENT_DATE()
ORDER BY tblstock.stock DESC, tblstock.updated ASC
i will be grateful if someone can help me please.
Have you thought about adding a column to the tblcompany table which records the last entry for that company in the tblstock table.
Can't see that pic (blocked by proxy I think), but from your description it sounds like a "the row having the groupwise max" problem.
Check out this thread: http://www.sitepoint.com/forums/mysql-182/collating-data-three-tables-763414.html