SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member Prithivi's Avatar
    Join Date
    Feb 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy MySQL Query problem [group and MAX()]

    Dear All,

    I have been attempting to resolve this for 48 hours and i have humbly to admit i need some help!

    I am attempting to return the latest additions to several groups in a table where the var pid = the searched $var
    The query i use groups items fine but return the FIRST record for that group not the LAST.
    dateadded is a timestamp - so i queried integer values as a trial - same problem.

    $query = "SELECT * , MAX(dateadded) FROM items WHERE pid = '$pid' GROUP BY item_ver";

    If anybody has some clue where i am going wrong i would be most grateful!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    combining GROUP BY with the dreaded, evil "select star" is the cause of your problem
    Code:
    SELECT item_ver
         , MAX(dateadded) 
      FROM items 
     WHERE pid = $pid -- note no quotes 
    GROUP 
        BY item_ver
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member Prithivi's Avatar
    Join Date
    Feb 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear R937,

    Much appreciated response. The 'DeathStar' could have been the problem,
    However... I tried it and it just thew up another error.
    There could be 3 or more groups associated with $pid so the query needs to find the max for each group where pid=pid... [you can see even now pseudocde in my head!]

    $query = "SELECT item_ver, MAX(dateadded) FROM items WHERE pid = $pid GROUP BY item_ver";

    $result = mysql_query($query);
    $num = mysql_num_rows ($result);

    if ($num > 0 ) {
    $i=0;

    while ($i < $num) {
    $item_id = mysql_result($result,$i,"item_id");
    $po_id = mysql_result($result,$i,"po_id");
    $usid_item = mysql_result($result,$i,"user_id_item");
    $part_no = mysql_result($result,$i,"part_no");
    $dateadded = mysql_result($result,$i,"dateadded");
    $qnty = mysql_result($result,$i,"qnty");
    $etd1 = mysql_result($result,$i,"etd");
    $eta1 = mysql_result($result,$i,"eta");
    $etd = date("d-m-Y", strtotime($etd1));
    $eta = date("d-m-Y", strtotime($eta1));
    $port = mysql_result($result,$i,"port");
    $vessel = mysql_result($result,$i,"vessel");
    $b_l = mysql_result($result,$i,"b_l");
    $del_to = mysql_result($result,$i,"del_to");
    $notes1 = mysql_result($result,$i,"notes");

    $item_ver = mysql_result($result,$i,"item_ver");


    vars then shoved out on an echo...

    If this sheds any light on the problem

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i don't do php but it looks like you want not only the latest dateadded for each item_ver, but also a whole bunch of additional columns

    this seems to be another example of the classic "rows holding the groupwise max of a column" problem

    there are many ways of doing this, but this is the way i would do it --
    Code:
    SELECT i.item_id
         , i.po_id
         , i.user_id_item
         , i.part_no
         , i.dateadded
         , i.qnty
         , i.etd
         , i.eta
         , i.port
         , i.vessel
         , i.b_l
         , i.del_to
         , i.notes
         , i.item_ver
      FROM ( SELECT item_ver
                  , MAX(dateadded) AS max_dateadded
               FROM items 
              WHERE pid = $pid 
             GROUP 
                 BY item_ver ) AS m
    INNER
      JOIN items AS i
        ON i.item_ver = m.item_ver
       AND i.dateadded = m.max_dateadded
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member Prithivi's Avatar
    Join Date
    Feb 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just tried multiple variations and received a
    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

    กก Help !!

  6. #6
    SitePoint Member Prithivi's Avatar
    Join Date
    Feb 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    R937.

    I will go away and revise what i am doing.
    When an SQL consultant tells you you're wrong...
    I shall post any further results after trying harder!

    Cheers Dude

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Prithivi View Post
    กก Help !!
    try the query in post #4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member Prithivi's Avatar
    Join Date
    Feb 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear R937,

    Your suggestion actually did EXACTLY what i needed.
    Dont know if i can send you a beer token, but if i can do so just let me know.
    Thankyou very much.

    [i was serious about the beer !]


Tags for this Thread

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
  •