select oldest and newest record based on id and timestamp

[matthewst]

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++;}
?>

[marmite]

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...

[matthewst]

hmmmm...

Not the reply I was hoping for. Any ideas on how to integrate two serparate queries.

[Barand]

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 

[rbragg]

*deleted* wrong thread