matthewst Posted April 23, 2007 Share Posted April 23, 2007 I need to select records from a database based on an id number and timestamp I can select based on id without a problem, whare I need help is selecting only the records with the first and latest timestamp. Example: table 1 id-------data--------timestamp 1--------xxx---------1111 1--------xxx---------1112 1--------xxx---------1113 2--------xxx---------1111 2--------xxx---------1112 2--------xxx---------1113 I only want to display records: 1--------xxx---------1111 1--------xxx---------1113 2--------xxx---------1111 2--------xxx---------1113 My current code displays all the records: <?php $tableid=68; echo "<center><strong>$tableid</strong><br><br></center>"; $query="SELECT * FROM ad_order WHERE cust_id=$tableid"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; while ($i < $num) {$id=mysql_result($result,$i,"id"); ////////////////////////// /////display only the first and last records of table 68 ///////////////////////// echo "<center>$id<br></center>"; $i++;} ?> Quote Link to comment Share on other sites More sharing options...
marmite Posted April 23, 2007 Share Posted April 23, 2007 Hmm, that seems hard. I think there are two (inelegant) solutions. Call MAX(timestamps) and MIN(timestamps) from your DB in two separate queries and then integrate them (I've forgotten the term for this, but you can make a "temporary table" almost). Either that or lots of pre-processing, where you scroll through the data putting it into a new array when you find a min or max. Not easy... Quote Link to comment Share on other sites More sharing options...
matthewst Posted April 23, 2007 Author Share Posted April 23, 2007 hmmmm... Not the reply I was hoping for. Any ideas on how to integrate two serparate queries. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 23, 2007 Share Posted April 23, 2007 try SELECT r.ID, r.data, r.timestamp FROM table r INNER JOIN (SELECT ID, MIN(timestamp ) as date FROM table GROUP BY ID UNION SELECT ID, MAX(timestamp ) as date FROM table GROUP BY ID) as a ON r.ID = a.ID and r.timestamp = a.date ORDER BY r.ID, r.timestamp Quote Link to comment Share on other sites More sharing options...
rbragg Posted April 25, 2007 Share Posted April 25, 2007 *deleted* wrong thread Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.