I would need to be able to use this function in the WHERE clause instead than in the SELECT clause. Is there a way to do this? Meaning, to find the maximum value of a column used in the WHERE clause?
is there a way? possibly, but probably not
you can use the MAX function in the HAVING clause without problems
;)
ya… Is the way to calculate the maximum value
SELECT
c.idCharities,
c.charityName,
COUNT(g.idGreetings) AS totalGreetings,
MAX(cp.paymentToDate) AS lastPaymentToDate
FROM Greetings g
INNER JOIN Charities c ON g.idCharities = c.idCharities
LEFT JOIN CharityPayments cp ON cp.idCharities = c.idCharities
WHERE (
(g.sendDate > (
SELECT MAX(cp2.paymentToDate) AS lastPaymentToDate
FROM CharityPayments cp2
WHERE cp2.idCharities = c.idCharities
)
) OR (
SELECT MAX(cp3.paymentToDate) AS lastPaymentToDate
FROM CharityPayments cp3
WHERE cp3.idCharities = c.idCharities
) IS NULL)
GROUP BY g.idCharities
ORDER BY c.charityName;
select * from my_table having my_date = max(my_date)
select something
, anything
, but
, please
, not
, the
, dreaded
, evil
, “select star”
from my_table
where my_date =
( select max(my_date)
from my_table )
select something
, anything
, but
, please
, not
, the
, dreaded
, evil
, “select star”
from my_table
where my_date =
( select max(my_date)
from my_table ):D:rofl:
Thank you 
I’m going to ask this question here, since it’s connected to my last one. I have a table “users” with columns ssn (key) and others, and a table “donations” with columns user, amount and date. To help you in this example, suppose that we have 3 users, USER1, USER2 and USER3 in the first table, and 3 entries in the second table:
USER1 100.00 2011-02-22
USER1 200.00 2010-02-20
USER2 100.00 2010-02-20
I want to return all the users who wither have never made a donation or have made a donation, but the last one was made more than one year ago (so, USER2 and USER3).
This is my query:
SELECT DISTINCT ssn
FROM users LEFT JOIN donations
ON users.cf = donations.user
WHERE donations.date IS NULL
OR '" . $year_ago . "' > (SELECT MAX(date) FROM donations)
$year_ago contains the current date, moved back of one year.
This query return only USER3, instead that both USER3 and USER2.
i’ll bet it’s the same one
okay, this is progress, but your subquery selects the latest date by any user
here, try this –
SELECT users.ssn
FROM users
LEFT OUTER
JOIN ( SELECT donations.user
, MAX(donations.date) AS latest
FROM donations
GROUP
BY donations.user ) qual
ON qual.user = users.cf
AND qual.latest > CURRENT_DATE - INTERVAL 1 YEAR
WHERE qual.user IS NULL
Nope, this is a new one, I had already solved the problem connected to my first question 
here, try this –
SELECT users.ssn
FROM users
LEFT OUTER
JOIN ( SELECT donations.user
, MAX(donations.date) AS latest
FROM donations
GROUP
BY donations.user ) qual
ON qual.user = users.cf
AND qual.latest > CURRENT_DATE - INTERVAL 1 YEAR
WHERE qual.user IS NULL
Of course it works, but I don’t understand why 
never seen a subquery in the FROM clause before?
it’s called a derived table, and in this query it has an alias, qual, which is used in joining it to the users table
the rest of it is a basic “unmatched” left outer join
Yes, but I have two questions:
-
if there’s a way to achieve this using a subquery in the WHERE clause instead than in the FROM clause
-
I don’t understand is this part of the query:
ON qual.user = users.cf
AND qual.latest > CURRENT_DATE - INTERVAL 1 YEAR
WHERE qual.user IS NULL
specifically the WHERE clause. The nested SELECT statement returns the ssn of the users who have made donations, together with the date of their last donation, so qual.user can’t be NULL. Why do you ask for it to be NULL in the WHERE clause then?
Also, I was thinking about doing something like this (probably won’t work):
ON qual.user = users.cf
WHERE qual.user IS NULL
OR qual.latest > CURRENT_DATE - INTERVAL 1 YEAR
Meaning make the WHERE clause more specific and the ON clause less specific.
that would be a NOT EXISTS subquery, yes
- Why do you ask for it to be NULL in the WHERE clause then?
because you want an unmatched left outer join
the left outer join specifies what you don’t want to find, and the WHERE conditions filters out the ones where you did find it
i realize that the combination of negatives there might cause confusion, especially if this is your first encounter with an unmatched left outer join
I’m getting a headache XD
Edit: I understand now.
Since we’re here, here’s one more thing Suppose that I want to build the “opposite” query, i.e. find the users who have made a donation in the last year, is this query OK?
SELECT ssn
FROM users INNER JOIN donations
ON users.cf = donations.user
HAVING MAX(donations.date) >= CURRENT_DATE - INTERVAL 1 YEAR
no, it’s not
use HAVING only with GROUP BY
try this –
SELECT users.ssn
FROM users
INNER
JOIN donations
ON donations.user = users.cf
AND donations.date >= CURRENT_DATE - INTERVAL 1 YEAR
GROUP
BY users.ssn
where, i can hear you asking, did the MAX go?
ah, sql, let me count the ways…
I guess you don’t need it in this query because you just need to find if a user has made a donation in the last year.
What about this query then
SELECT ssn
FROM users INNER JOIN donations
ON users.cf = donations.user
WHERE donations.date >= CURRENT_DATE - INTERVAL 1 YEAR
that’s almost the same as the query i gave you in post #16
i suggest you run them both and compare output results
Right, I forgot to add the DISTINCT keyword in my last example