CODEWITHSUNDEEP
phpmysqlsqlstored-procedures
Prog_Rookie
Prog_Rookie

Reputation: 448

Select max and min values in one sql query

i have a table that has records with its timestamp. now i have a requirement to get the oldest record and the newest record

9/10/2014 6:54
9/10/2014 6:53
9/10/2014 6:51
9/8/2014 8:09
9/8/2014 7:00
9/5/2014 7:38
9/5/2014 3:57
9/5/2014 3:51
9/4/2014 11:09
9/4/2014 8:39

currently this is how i obtain them by sending two database calls which slows downs processing

$new_timestamp = mysql_query("SELECT TIMESTAMP FROM $rectable ORDER BY TIMESTAMP DESC LIMIT 1");
$col_old = mysql_fetch_assoc($new_timestamp);
$old = $col_new['TIMESTAMP'];

$new_timestamp1 = mysql_query("SELECT TIMESTAMP FROM $rectable ORDER BY TIMESTAMP ASC LIMIT 1");
$col_new = mysql_fetch_assoc($new_timestamp1);
$new = $col_new['TIMESTAMP'];

is there any way to optimize this code and fullfiill requirement without sending two database calls, througha special query or a stored proceedure

Upvotes: 2

Views: 2276

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32402

You can use max and min to get the newest and oldest timestamps

select max(timestamp), min(timestamp) from mytable

Upvotes: 5

RNK
RNK

Reputation: 5792

Try with UNION

select TIMESTAMP as old FROM $rectracktable ORDER BY TIMESTAMP DESC LIMIT 1
union all
select TIMESTAMP as new FROM $rectable ORDER BY TIMESTAMP ASC LIMIT 1

Upvotes: 1

Related Questions