CODEWITHSUNDEEP
mysql
diepnguyen
diepnguyen

Reputation: 53

How to get first record and last record in a date

I have a table structured in below format : 

**ID        StationID         Status        UpdateTime**
  1             1              xxxx      2014-09-25 00:01:05
  2             1              xxxy      2014-09-25 01:05:18
  3             1              xxxz      2014-09-25 09:23:05
  4             1              xxxx      2014-09-25 20:01:05
  5             1              xxxz      2014-09-25 23:21:34
  6             1              xxxz      2014-09-26 00:01:00
  7             1              xxxx      2014-09-26 01:45:24
  8             1              xxxy      2014-09-26 14:01:43
  9             1              xxxx      2014-09-26 22:01:27

For each day, I want to select first record and last record. Here is result:

**ID        StationID         Status        UpdateTime**
  1             1              xxxx      2014-09-25 00:01:05
  5             1              xxxy      2014-09-25 23:21:34
  6             1              xxxz      2014-09-26 00:01:00
  9             1              xxxx      2014-09-26 22:01:27

Thanks all;

Upvotes: 4

Views: 1518

Answers (5)

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

If you want to do this by union

SELECT ID, StationId, Status, max(UpdateTime) FROM YOUR_TABLE GROUP BY DATE(UpdateTime)
UNION ALL
SELECT ID, StationId, Status, min(UpdateTime) FROM YOUR_TABLE GROUP BY DATE(UpdateTime)

Just to add, to increase the efiiciency use UNION ALL rather than UNION (or UNION DISTINCT) as UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index.

If you want to do this by union and subquery

select * from table where id IN ( select max(id) from table GROUP BY DATE(UpdateTime) )
union all
select * from table where id IN ( select min(id) from table GROUP BY DATE(UpdateTime) )

If you want to do it by subquery

SELECT * from table
WHERE  id IN ( select max(id) from table GROUP BY DATE(UpdateTime) )
OR id IN ( select min(id) from table GROUP BY DATE(UpdateTime) )

Upvotes: 1

Shafeeque
Shafeeque

Reputation: 2069

May be this will work

SELECT ID,StationId,Status,MAX(UpdateTime) FROM YOUR_TABLE GROUP BY DATE(UpdateTime)
UNION ALL
SELECT ID,StationId,Status,MIN(UpdateTime) FROM YOUR_TABLE GROUP BY DATE(UpdateTime)

Upvotes: 1

Giles
Giles

Reputation: 1667

select * from mytable 
where UpTime = (select min(UpTime) from mytable group by date_format(UpTime,'%Y-%m-%d')) 
or UpTime = (select max(UpTime) from mytable group by date_format(UpTime,'%Y-%m-%d')) 
order by id

Upvotes: 1

neshkeev
neshkeev

Reputation: 6476

Try this:

select *
  from YOUR_TABLE yt
  join (select min(UpdateTime) minUpdateTime, max(UpdateTime) maxUpdateTime
          from YOUR_TABLE
         group by DATE_FORMAT(UpdateTime, '%Y-%m-%d')
       ) aggyt on aggyt.minUpdateTime = yt.UpdateTime or aggyt.maxUpdateTime = yt.UpdateTime

Upvotes: 2

nesreen
nesreen

Reputation: 201

select * from mytable 
where UpTime= (select min(UpTime) from mytable group by date_format(UpTime,'%Y-%m-%d')) 
or UpTime= (select max(UpTime) from mytable group by date_format(UpTime,'%Y-%m-%d')) 
order by id

Upvotes: 1

Related Questions