SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    complicated SELECT statement: display records from multiple table

    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:
    Code:
    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.

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you thought about adding a column to the tblcompany table which records the last entry for that company in the tblstock table.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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/mysq...es-763414.html


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •