CODEWITHSUNDEEP
mysql
Jay Doshi
Jay Doshi

Reputation: 724

How to find Last records of ID with Group by and check with time?

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 :

Assume my current time is 17:50:00, So my below result inserted before half hour of my current time.

id  | a_id | amount
------------------------
3   |   1  | 2016-01-09 17:42:14

How can I get this kind of output?

Upvotes: 1

Views: 62

Answers (4)

Strawberry
Strawberry

Reputation: 33945

In case anyone's over-thinking this... 

SELECT *
  FROM main 
 WHERE datetime < NOW() - INTERVAL 30 MINUTE 
 ORDER
   BY datetime DESC 
  LIMIT 1;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Something like this should work:

SELECT *
FROM mytable
WHERE `datetime` BETWEEN DATE_ADD(NOW(), INTERVAL -30 MINUTE) AND NOW()

Demo here

Edit:

To get the latest-per-group records and then find whether these records fall within a specified interval, you can use the following query:

SELECT id, a_id, `datetime`
FROM (
  SELECT id, a_id, `datetime`,
         @rn := IF(@aid = a_id, @rn + 1,
                   IF(@aid := a_id, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @aid := 0) AS vars
  ORDER BY a_id, `datetime` DESC) AS t
WHERE t.rn = 1 AND 
      `datetime` BETWEEN DATE_ADD(NOW(), INTERVAL -30 MINUTE) AND NOW()

Demo here

Upvotes: 0

Sagar Patel
Sagar Patel

Reputation: 461

something like should work if max(a_id) dosent work then you should also put there datetime field name but first convert that in string using sql

SELECT p1.* , p1.a_id as a_id
            FROM tbl_name p1
            INNER JOIN
            (
                SELECT max(a_id) as MaxVal
                FROM tbl_name
                GROUP BY a_id
            ) p2
              ON p1.id = p2.id
              AND p1.a_id = p2.MaxVal
            WHERE p1.datetime >= DATE_SUB(NOW(),INTERVAL 0.5 HOUR)

Upvotes: 0

Vipin Jain
Vipin Jain

Reputation: 3756

Try This

SELECT *
FROM table_name
WHERE `date_time` 
BETWEEN DATE_ADD(NOW(), INTERVAL -30 MINUTE) AND NOW();

Upvotes: 1

Related Questions