MAX function in WHERE clause

[D3V4]

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?

[r937]

is there a way? possibly, but probably not

you can use the MAX function in the HAVING clause without problems

[sankarg]

ya...... Is the way to calculate the maximum value

[sankarg]

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;

[sankarg]

select * from my_table having my_date = max(my_date)

[sankarg]

select something
, anything
, but
, please
, not
, the
, dreaded
, evil
, "select star"
from my_table
where my_date =
( select max(my_date)
from my_table )

[sankarg]

select something
, anything
, but
, please
, not
, the
, dreaded
, evil
, "select star"
from my_table
where my_date =
( select max(my_date)
from my_table )

[D3V4]

is there a way? possibly, but probably not

you can use the MAX function in the HAVING clause without problems

Thank you

[D3V4]

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.

[r937]

I'm going to ask this question here, since it's connected to my last one.

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

[D3V4]

i'll bet it's the same one

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

[r937]

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

[D3V4]

never seen a subquery in the FROM clause before?

Yes, but I have two questions:

1. if there's a way to achieve this using a subquery in the WHERE clause instead than in the FROM clause

2. 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.

[r937]

1. if there's a way to achieve this using a subquery in the WHERE clause instead than in the FROM clause

that would be a NOT EXISTS subquery, yes

2. 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

[D3V4]

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

[r937]

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...

[D3V4]

where, i can hear you asking, did the MAX go?

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

[r937]

What about this query then

that's almost the same as the query i gave you in post #16

i suggest you run them both and compare output results

[D3V4]

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