2

This is my main table which contain this type of records

id  | a_id | datetime
------------------------
1   |   1  | 2016-01-07 15:42:14
2   |   1  | 2016-01-08 16:42:14
3   |   1  | 2016-01-09 17:42:14
4   |   2  | 2016-01-07 15:42:14
5   |   2  | 2016-01-08 16:42:14
6   |   2  | 2016-01-09 17:42:14
7   |   2  | 2016-01-10 18:42:14
8   |   2  | 2016-01-11 19:42:14

and I want output like : 

id  | a_id | amount
------------------------
3   |   1  | 2016-01-09 17:42:14
8   |   2  | 2016-01-11 19:42:14

How can I get this kind of output?

I tried by this SELECT * FROM (SELECT * FROM table ORDER BY datetime DESC) as tbl GROUP BY a_id

In additionally I want to check that Is there any record inserted in table before Half Hour.

Jay Doshi
  • 666
  • 1
  • 5
  • 16
  • https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html – Strawberry Feb 24 '16 at 12:21
  • Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Strawberry Feb 24 '16 at 12:26

1 Answers1

0

If you don't care about performance, you can use IN operator w/ column datetime and subquery w/ MAX().

SELECT 
* 
FROM 
    tbl
WHERE 
    `datetime` IN
            (
                SELECT MAX(tbl2.`datetime`)
                FROM tbl tbl2
                WHERE tbl.a_id = tbl2.a_id
            )
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Dylan Su
  • 5,975
  • 1
  • 16
  • 25
  • I already posted this idea, and what will happen if another a_id has the same date time as the max date time from a specific a_id ? – sagi Feb 24 '16 at 12:18
  • since a different a_id, the corresponding tbl.a_id is different, and the two duplicate date time will not appear in the same subquery result set. – Dylan Su Feb 24 '16 at 12:22