SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MAX function in WHERE clause

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    is there a way? possibly, but probably not

    you can use the MAX function in the HAVING clause without problems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

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

  4. #4
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;

  5. #5
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select * from my_table having my_date = max(my_date)

  6. #6
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select something
    , anything
    , but
    , please
    , not
    , the
    , dreaded
    , evil
    , "select star"
    from my_table
    where my_date =
    ( select max(my_date)
    from my_table )

  7. #7
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select something
    , anything
    , but
    , please
    , not
    , the
    , dreaded
    , evil
    , "select star"
    from my_table
    where my_date =
    ( select max(my_date)
    from my_table )

  8. #8
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    is there a way? possibly, but probably not

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

  9. #9
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by D3V4 View Post
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 --
    Code:
    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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:

    Code:
    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):

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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by D3V4 View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Code:
    SELECT ssn
    FROM users INNER JOIN donations
    ON users.cf = donations.user
    HAVING MAX(donations.date) >= CURRENT_DATE - INTERVAL 1 YEAR

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, it's not

    use HAVING only with GROUP BY

    try this --
    Code:
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

    Code:
    SELECT ssn
    FROM users INNER JOIN donations
    ON users.cf = donations.user
    WHERE donations.date >= CURRENT_DATE - INTERVAL 1 YEAR

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by D3V4 View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •