MYSQL request | GROUP BY DAY

Question

I have a table: orders, and need to make a request and get other table. My DB table:

id  close
1   2012-05-29 03:11:15
2   2012-05-30 03:11:40
3   2012-05-31 03:12:10
4   2012-05-31 03:14:13
5   2012-05-31 03:16:50
6   2012-05-31 03:40:07     
7   2012-05-31 05:22:18
8   2012-05-31 05:22:22
9   2012-05-31 05:22:50
...

I need to make a request and get this table (GROUP BY DAY(close)):

1   2012-05-29 03:11:15
2   2012-05-30 03:11:40
9   2012-05-31 05:22:50 /*This is a last record on this day (05-31)*/

Thanks!

If I make this request:

SELECT id, close
FROM `orders`
GROUP BY DAY(close)
ORDER BY id ASC

I will get this table:

1   2012-05-29 03:11:15
2   2012-05-30 03:11:40
3   2012-05-31 03:12:10

My question is: how can I get this table from request: 1 2012-05-29 03:11:15 2 2012-05-30 03:11:40 9 2012-05-31 05:22:50 /*This is a last record on this day (05-31)*/ — XTRUST.ORG, May 31 '12 at 10:42

Michał Powaga · Answer 1 · 2012-05-31T10:48:47.050

2

Try:

select t1.* 
from orders t1
join (
    select max(close) as close
    from orders
    group by date(close)
) t2 on t1.close = t2.close

Working example: http://sqlfiddle.com/#!2/e799a/1

edited May 31 '12 at 10:48

answered May 31 '12 at 10:42

Michał Powaga

22,561
8
51
62

@user889349 `tab` was table name for test only, now I've changed it to `orders`. `t1` and `t2` are table aliases more about it is there: [SQL Alias](http://www.w3schools.com/sql/sql_alias.asp). – Michał Powaga May 31 '12 at 10:51
@user889349 I wonder why have you removed "acceptance" from my answer? especially that there is no guarantee that sephoy08 query always returns correct result set. More on ['MySQL `group by`'](http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html): *The server is free to choose any value from each group, so unless they are the same, **the values chosen are indeterminate***. – Michał Powaga May 31 '12 at 11:35

score 1 · Accepted Answer · answered May 31 '12 at 10:58

1

Try this, this will do.

SELECT 
    a.id,
    a.close
FROM
(
    SELECT 
        id,
        close
    FROM
        `orders`
    ORDER BY
        close DESC
) AS a
GROUP BY 
    DATE(a.close)
ORDER BY 
    a.id
ASC;

answered May 31 '12 at 10:58

sephoy08

1,104
7
16

@user889349 I'd recommend to read [12.16.3. `GROUP BY` and `HAVING` with Hidden Columns](http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html) especially: *The server is free to choose any value from each group, so unless they are the same, **the values chosen are indeterminate**. Furthermore, **the selection of values from each group cannot be influenced by adding an ORDER BY clause**. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.*. It's silly to rely on luck in the RDBMS world :-). – Michał Powaga May 31 '12 at 11:40

score 0 · Answer 3 · edited May 31 '12 at 16:03

0

Maybe this help:

SELECT temp.`close`
FROM (
SELECT `close`
FROM `orders`
ORDER BY `close` DESC
) AS temp
ORDER BY `close` ASC

edited May 31 '12 at 16:03

j0k

22,600
28
79
90

answered May 31 '12 at 11:14

Ashwani

1

MYSQL request | GROUP BY DAY

3 Answers3

Linked