MySQL select every day last row orderered by date DESC

Question

i have data,

Stamp                dataField1 dataFiel2
2012-05-23 23:59:59  ....        ....
2012-05-23 23:50:00  ....        ....
2012-05-24 24:43:23  ....        ....
2012-05-24 24:12:10  ....        ....
2012-05-25 25:15:00  ....        ....
2012-05-26 25:53:59  ....        ....

and i want to select every day last row and result should look li

Stamp                dataField1 dataFiel2
2012-05-23 23:59:59  ....        ....
2012-05-24 24:43:23  ....        ....
2012-05-26 25:53:59  ....        ....

What is the data type of the column `Stamp`? If it is a datetime, how it can be a time like `24:43:23` or `25:53:59`? — Mahmoud Gamal, Dec 19 '12 at 08:11
@MahmoudGamal : I believe OP was putting raw data and missed that... from 23 he increased time by 1... so it is 23...24...25 — Fahim Parkar, Dec 19 '12 at 08:28

score 0 · Answer 1 · answered Dec 19 '12 at 08:12

0

SELECT MAX(Stamp) as Last, other_column FROM table GROUP BY DAY(Stamp)

answered Dec 19 '12 at 08:12

Muhammad Raheel

19,823
7
67
103

score 0 · Answer 2 · answered Dec 19 '12 at 08:19

0

SELECT Stamp, dataField1, dataFiel2  FROM tableName 
GROUP BY Stamp ORDER BY Stamp ASC

answered Dec 19 '12 at 08:19

Sona Rijesh

1,041
7
32
62

Fahim Parkar · Accepted Answer · 2012-12-19T08:35:33.397

0

How about this?

SELECT MAX(Stamp) as Stamp, dataField1, dataField2
FROM tableName
GROUP BY DAYOFYEAR(Stamp)

I would say use DAYOFYEAR instead of DAY as everyone stated. For difference, see below link.

Demo

In this demo, DAY gives 3 rows which is INCORRECT as oppose to 4 rows using DAYOFYEAR.

edited Dec 19 '12 at 08:35

answered Dec 19 '12 at 08:19

Fahim Parkar

30,974
45
160
276

dataField1 and dataField2 can be from arbitrary rows in each day, not necessarily the last row of each day. – Barmar Dec 19 '12 at 08:24
@Barmar : I don't get what you want to say... – Fahim Parkar Dec 19 '12 at 08:26
This answer is incorrect as dataField1, dataField2 would be random values. – nowhere May 10 '16 at 13:46

score 0 · Answer 4 · answered Dec 19 '12 at 08:27

0

Try this query -

SELECT
  Stamp, dataField1, dataField2, dataField3
FROM
  (SELECT * FROM table ORDER BY Stamp DESC) t
GROUP BY
  DATE(Stamp)

answered Dec 19 '12 at 08:27

Devart

119,203
23
166
186

MySQL select every day last row orderered by date DESC

4 Answers4

Demo

Linked