CODEWITHSUNDEEP
mysqljoingroup-by
Alexander Karp
Alexander Karp

Reputation: 408

mysql max function in group by

I try to get items in mysql, I need to remove from result t2 items, that have status_id = 17 and status_change_date less then max date_created for this t1

t1:  
-----
|id |
-----
| 1 |
-----


t2:
---------------------------------------------------------------
| id | t1_id | status_change_date | status_id |  date_created | 
---------------------------------------------------------------
|  1 |     1 | 2006-02-12         |        17 |  2004-02-12   | 
|  2 |     1 | 2006-02-12         |        17 |  2006-02-12   | 
|  3 |     1 | 2010-02-12         |        17 |  2007-02-12   | 
|  4 |     1 | 2006-02-12         |        17 |  2008-02-12   | 
---------------------------------------------------------------

so in result I should to see only item with id 3, cause only in this case status_change_date is after max date_created for items, that have reference to t1 item with id 1

SELECT t1.*, t2.* 
FROM t1 JOIN 
    (
     SELECT t2.*, 
     MAX(date_created) AS max_date FROM t2 WHERE t2.id NOT IN
         (
             SELECT t2.id  FROM t2
             WHERE
                 status_id = 17 
             and status_change_date < max_date
         ) 
     GROUP BY t1_id
    ) t2 ON t1.id = t2.t1_id

But i got error cause sql don't know what is max_date. How I should to get this value?

Upvotes: 0

Views: 66

Answers (3)

Gaurav Sharma
Gaurav Sharma

Reputation: 41

You can go with anyone of the following queries: 

SELECT t1.*, 
       t2.*, 
       Max(date_created) AS max_date 
FROM   t1 
       JOIN t2 
         ON t1.id = t2.t1_id 
WHERE  status_id = 17 
GROUP  BY status_change_date 
HAVING status_change_date > max_date;

Or

SELECT t1.*, 
       t2.* 
FROM   t1 
       JOIN t2 
         ON t1.id = t2.t1_id 
WHERE  status_id = 17 
       AND status_change_date > (SELECT Max(date_created) 
                                 FROM   t2);

Upvotes: 0

SaGaR.K
SaGaR.K

Reputation: 1

You can store value to temporary variable and use to compare:

SELECT t1.*, t2.* 
FROM t1 JOIN 
    (
     SELECT t2.*, 
     @max_date:=MAX(date_created) AS max_date FROM t2 WHERE t2.id NOT IN
         (
             SELECT t2.id  FROM t2
             WHERE
                 status_id = 17 
             and status_change_date < @max_date
         ) 
     GROUP BY t1_id
    ) t2 ON t1.id = t2.t1_id

Upvotes: 0

Trudge
Trudge

Reputation: 57

From what I understand of joins, your 'from' should come AFTER your 'select' statements.

select *,left(name,1) as L1 from artists order by name limit 5;

Upvotes: 1

Related Questions