[SOLVED] Query with a GROUP BY

[SirChick]

I have a query which is meant to get the reported players which it does.. then what it does is it sorts them in order by "last post" so... say a reported had been replied to yesterday.. and one was replied today. Then the one posted yesterday will be above the one that was replied to today (i do this by ORDER BY ASC) which works.

Now the problem is... say theres 2 replies for one report like the staff typed "this is your last warning" and the user says "ok" ... my query is loading the report twice into my list but its not meant to do that...

 

One table has the messages posted for the report (reportedsituation) the other table carries things like the reason who reported them and other information that staff only need to see when dealing with it. (reportedusers)

 

This is what i tried although it works it gets the same report twice rather than only one...hope you can help me:

I can show you my table structure if needs be..

 

$GetAppeals = mysql_query("SELECT * FROM reportedusers,reportsituation WHERE reportedusers.FrozenBy != 0 AND reporteduser.RecordID == reportedsituation.RecordID GROUP BY RecordID ORDER BY reportsituation.SaidTime ASC")
or die(mysql_error());

[SirChick]

Ok just to update you:

 

I tried this to get it working:

 

$GetAppeals = mysql_query("SELECT * FROM reportedusers,reportsituation WHERE reportedusers.FrozenBy != 0 AND reportedusers.RecordID == reportsituation.RecordID GROUP BY reportedusers.RecordID ORDER BY reportsituation.SaidTime ASC")
or die(mysql_error());

 

But i get this:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== reportsituation.RecordID GROUP BY reportedusers.RecordID ORDER BY reportsitua' at line 1

 

Is == the correct syntax?

[btherl]

SQL uses =, not ==

 

The other issue is that you are ordering by a column which you did not aggregate.  If there are 2 rows with the same RecordID but different SaidTime, which should it order by?

 

Try this query:

 

$GetAppeals = mysql_query("SELECT reportedusers.RecordID, MAX(reportsituation.SaidTime) AS MaxSaidTime FROM reportedusers,reportsituation WHERE reportedusers.FrozenBy != 0 AND reportedusers.RecordID = reportsituation.RecordID GROUP BY reportedusers.RecordID ORDER BY MAX(reportsituation.SaidTime) ASC")

[SirChick]

What can you explain what you mean by that? I would have thought it would just order SaidTime anyway?

[btherl]

Let's say you have this data (I'm making it a date for simplicity)

 

RecordID  SaidTime

1            2007-12-01

1            2007-12-10

2            2007-12-05

 

Now you group it by RecordID and order it by SaidTime.  What order will the results be in, and what will the value of SaidTime be for each RecordID?

[SirChick]

It would have to be the most recent so the 2007-12-10 for record ID 1 because that represents when the most recent message occurred.

[btherl]

Ok, then you need to order by MAX(SaidTime), rather than SaidTime.  That tells SQL that you want the most recent (the maximum) of all the possible values.

 

If you don't specify MAX() then it may not give an error, but even if it works, you may find that you get values in the wrong order.  THat'll be because it picks a random value to order by, rather than always picking the max.