[RESOLVED] Query order problem

Aurasia

I've got a table in my dB which looks like this:

and using this code:

$query_Recordset1 = "SELECT * FROM datacore GROUP BY project ORDER BY `timestamp` DESC";

I'm getting a weird order. The idea here, is to get back the most recent file for each project based on the timestamp. I've looked over the numbers and my results bring back 2 projects. I only have two projects, so that's fine, but the row that's coming back from each project isn't based on the timestamps within each project.

I think this is because it's grouping the results first, then it's ordering the results by the timestamps. What I want it to do is order by timestamps, then get the newest image for each project.

I've been in the Manual looking up group by and order by ... but haven't found anything helpful yet. Any thoughts?

trexx

yes. you've to select the max(timestamp) first. if your database supports subqueries it may look like this:

select 
    id, name, project, type, catagory, 
    keywords, description, image, live, timestamp 
from  datacore d,
    (select project, max(timestamp) max_timestamp
     from datacore
     group by project) as t
where d.timestamp=t.max_timestamp
  and d.project=t.project
order by timestamp desc

Lars_Berg

select <columnsOfInterest>
from datacore dc
where  `timestamp` in
(select max(`timestamp`)
  from datacore
 where projectId = dc.projectId)

Aurasia

Thank you guys for your posts.

I tried them both and ... well - I kept getting errors with the syntax. I know you didn't mean to type them in to be functional, but I couldn't get them to work. 🙁 I spent a little time on the MYSQL documentation, and I'll try and read up some more on SELECT MAX(), so thank you for pointing me in that direction.

I ended up solving my problem in a different fashion - this is the way that made sense to me ... wince not exactly elegant ... but it does work now:

<table width="10" border="0" cellspacing="0" cellpadding="6">
  <tr>
    <?php $column = 0; do { $column++;
      $grab_id = $row_AvailableProjects['project'];
      mysql_select_db($database_DigitalStarFire_Con, $DigitalStarFire_Con);
      $query_ProjectImage = "SELECT name, id, project, image FROM datacore 
           WHERE project = '$grab_id' ORDER BY `timestamp` DESC";
      $ProjectImage = mysql_query($query_ProjectImage, $DigitalStarFire_Con) or 
           die(mysql_error());
      $row_ProjectImage = mysql_fetch_assoc($ProjectImage);
      $totalRows_ProjectImage = mysql_num_rows($ProjectImage);
?>


<td><div align="center"><a href="image.php?id=<?php echo $row_ProjectImage['id']; ?>"><img src="<?php echo str_replace('uploads/', 'uploads/_thumb_', $row_ProjectImage['image']);?>" border="0"></a><br>
<?php echo $row_ProjectImage['project']; ?><a href="image.php?id=<?php echo $row_ProjectImage['id'];?>"><br>
</a></div></td>
<?php if ($column == 5) { echo '</tr><tr>'; $column = 0;}?>

<?php } while ($row_AvailableProjects = mysql_fetch_assoc($AvailableProjects)); ?>
</tr>
</table>

Thanks again for the help. You people are fantastic.

Peace

trexx

hmm... which database and version do you have?