CODEWITHSUNDEEP
mysqlsql
G Jay
G Jay

Reputation: 137

Select max,min, last value from a column in mysql table

table format

id   time_stamp
3    2013-09-05 12:00:00
5    2013-09-06 12:00:00 
12   2013-09-07 12:00:00
2    2013-09-08 12:00:00
5    2013-09-09 12:00:00
8    2013-09-10 12:00:00

From the above table i want select min(id), max(id), last id, last time_stamp in single mysql select query statement

Needed output is:

min   max  last(val)  last(time_stamp)
2     12   8          2013-09-09 12:00:00

i used following Query

select id, min(id),max(id), time_stamp from table order by time_stamp limit 1

I am getting wrong latest id value 3 instead of 8

Check this if below SQL fiddle
http://www.sqlfiddle.com/#!2/e9cb1/2/0

Upvotes: 3

Views: 40861

Answers (2)

Praveen Lobo
Praveen Lobo

Reputation: 7187

Assuming you mentioned the last(time_stamp) incorrectly in your question - Get the max, min as usual and then find out the last id and time stamp in a subquery which you can then JOIN to get all result in one row.

SQL Fiddle

MySQL 5.5.32 Schema Setup:

create table t (id int, time_stamp datetime);

insert into  t values(3,    '2013-09-05 12:00:00');
insert into  t values(5,    '2013-09-06 12:00:00');
insert into  t values(12,   '2013-09-07 12:00:00');
insert into  t values(2,    '2013-09-08 12:00:00');
insert into  t values(5,    '2013-09-09 12:00:00');
insert into  t values(8,    '2013-09-10 12:00:00');

Query 1:

SELECT MIN(t.id), MAX(t.id), latest.id, latest.time_stamp
FROM t  JOIN (
  SELECT t.id, t.time_stamp
  FROM t
  ORDER BY time_stamp DESC
  LIMIT 1) latest

Results:

| MIN(T.ID) | MAX(T.ID) | ID |                       TIME_STAMP |
|-----------|-----------|----|----------------------------------|
|         2 |        12 |  8 | September, 10 2013 12:00:00+0000 |

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Here is a method, using the substring_index()/group_concat() trick:

select min(id), max(id),
       substring_index(group_concat(id order by time_stamp desc), ',', 1) as lastid
from table ;

Upvotes: 8

Related Questions