CODEWITHSUNDEEP
mysqlsql
Trollwut
Trollwut

Reputation: 551

How to select none if condition is not?

I try to do a simple query to select from a database and only select rows which fit to the statement of being older than 12 months.

The table is like (mydate actually is a datetime field):

+----+---------+-------------+
| id | account | mydate      |
+----+---------+-------------+
| 1  | 1       | last week   | <-- this should disqualify account 1
| 2  | 1       | 2 years ago |
| 3  | 1       | 3 years ago |
| 4  | 2       | 2 years ago |
+----+---------+-------------+

And this is my query by now:

SELECT id, account, MAX(mydate)
FROM awesomeTable
WHERE
  mydate < SUBDATE(CURDATE(), INTERVAL 12 MONTH)
GROUP BY account

Seems nice. But the results are like following:

+----+---------+-------------+
| id | account | mydate      |
+----+---------+-------------+
| 2  | 1       | 2 years ago | <-- SQL doesnt understand me :(
| 4  | 2       | 2 years ago |
+----+---------+-------------+

But I don't want to let account 2 show up at all. I really just want to see accounts where mydate is older than 2 years.

With that query now it doesnt show ID 1 as this doesnt fit the condition in the WHERE clause. But it will show me the newest possible mydate of the other rows.

I want to get a list of all accounts which mydates are ALL older than 1 year. How can i achieve that?

Upvotes: 2

Views: 811

Answers (1)

Barmar
Barmar

Reputation: 781503

There WHERE condition is done to select the rows first, then MAX() is applied to those results. Use HAVING to operate on the results after aggregating.

SELECT id, account, MAX(mydate) AS maxdate
FROM awesometable
GROUP BY account
HAVING maxdate < DATE_SUB(NOW(), INTERVAL 12 MONTH)

Note that this will not necessarily show the id of the line with the maximum date. For that, you need a join:

SELECT a.id, a.account, a.mydate
FROM awesometable AS a
JOIN (
    SELECT account, MAX(mydate) AS maxdate
    FROM awesometable
    GROUP BY account
    HAVING maxdate < DATE_SUB(NOW(), INTERVAL 12 MONTH)) AS b
ON a.account = b.account AND a.mydate = b.maxdate

Upvotes: 3

Related Questions