MySQL Query problem [group and MAX()]

PHP
1

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

combining GROUP BY with the dreaded, evil “select star” is the cause of your problem

SELECT item_ver
     , MAX(dateadded) 
  FROM items 
 WHERE pid = $pid -- note no quotes 
GROUP 
    BY item_ver
3

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

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 –

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
5

I just tried multiple variations and received a
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

¡¡ Help !!

6

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

try the query in post #4 :wink:

8

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 !]