CODEWITHSUNDEEP
phpmysql
James
James

Reputation: 17

PHP - MySQL - GROUP BY / ORDER BY? wrong results output

I am rather new to MySQL, I have this problem, which I have searched for help with, but could not find the answer (possibly because I am not sure what to search for). I am sure there is a simple solution.

I have a simple MySQL table like this:

id   |   referenceid   |   status
40       104702            4
39       104720            2
38       104720            0
37       104719            2
36       104719            0
35       104702            2
34       104702            0
41       104719            5

I want to fetch an array that will produce the following output:

For each unique "referenceid" field, I want to know the highest "ID" (ie most recent) and its "Status".

So I have tried this query:

$result = mysql_query(
  "SELECT status,id,referenceid 
   FROM image_status_tracking 
   GROUP BY referenceid",$db);

while ($row = mysql_fetch_array($result)) {
  $id = $row['id'];
  $status = $row['status'];
  $referenceid = $row['referenceid'];
  echo "ID $id, REFID $referenceid, Status $status <br />";
}

which gives this output:

ID 40, REFID 104702, Status 4

ID 37, REFID 104719, Status 2

ID 39, REFID 104720, Status 2

But this is not the result I was hoping for. The ID and Status for referenceid 104719 should be 41 and 5, not 37 and 2.

I cannot work out how to get the most recent ID line output for each referenceid.

Something I noticed, in PHPMYADMIN, (and you can see in the table above), the IDs are listed in reverse order, with the latest ID at the bottom. I am not sure if that is relevant to this problem.

I hope this is clear, I am sure it is a simple matter.

Thank you for reading.

James

Upvotes: 0

Views: 1051

Answers (3)

Aman
Aman

Reputation: 76

Typing from memory so you might have to correct the syntax a little bit but the concept, i think, should work.

select * 
  from image_status_tracking 
 where id in (
              select max(id) from image_status_tracking 
              GROUP BY referenceid
             )

Upvotes: 4

Djomla
Djomla

Reputation: 616

Try this...

 SELECT MAX(ID), Status From image_status_tracking 
       GROUP BY referenceid

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your query does not do what you want it to do. For each reference id, it is selecting a random id and status. This is a (mis)Feature of MySQL called Hidden Columns.

What you want is more like:

select ist.*
from image_status_tracking ist join
     (SELECT referenceid, max(id) as maxid
      FROM image_status_tracking 
      GROUP BY referenceid
    ) r
    on ist.referenceid = r.referenceid

Upvotes: 1

Related Questions