Results 1 to 3 of 3

Thread: Mysql - MAX in WHERE section

  1. #1
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Mysql - MAX in WHERE section

    Hey, I don't think this is possible (as I think the WHERE section of an SQL query applies before your results are grouped but.... is there any way to use MAX or COUNT in the WHERE statement?

    It's not an ideal situation but we've got a database that imported records from a few separate databases and so have some duplication of data (well, not all of it is duplicated, but there's an overlap on name and surname hence why I am using this to group the items together).

    Code:
    SELECT p.name, p.surname, concat(`name`,' ',`surname`) as fullname, MAX(pd.status = 'deceased') as deaded 
    FROM person p 
    LEFT JOIN person_demographics pd on pd.person_id = p.person_id 
    WHERE pd.contact_approved = 1
    GROUP BY concat(`name`,' ',`surname`) 
    ORDER BY deaded asc, fullname asc
    The above basically says, if one of the person records of a certain name is flagged as 'deceased' (ie deaded will equal 1) then disregard all records of that name. What I'd like to be able to do is add a condition that's basically WHERE deaded != 1 [ or MAX(pd.status = 'deceased') != 1 ]. Is that possible at all?

  2. #2
    Pup
    Pup is offline
    woof
    Join Date
    Apr 2006
    Location
    West London
    Posts
    265
    Thanks
    18
    Thanked
    19 times in 15 posts
    • Pup's system
      • Motherboard:
      • Asus P8Z68-V Pro
      • CPU:
      • i7-2600K
      • Memory:
      • 8GiB Corsair Vengeance LP DDR3 1600MHz
      • Storage:
      • 2 x 1TB Samsung Spinpoint F3
      • Graphics card(s):
      • 2 x MSI GTX 560 Ti Twin Frozr II 1GiB
      • PSU:
      • Corsair HX850W
      • Case:
      • Antec P182
      • Operating System:
      • Windows 7 Professional x64
      • Monitor(s):
      • HP LP2475w & Iiyama Prolite E435s
      • Internet:
      • Virgin Media 20mb

    Re: Mysql - MAX in WHERE section

    Quote Originally Posted by Raz316 View Post
    is there any way to use MAX or COUNT in the WHERE statement?
    Yes, you need to use the HAVING clause. Essentially use it like a WHERE clause, but on aggregate functions, ie. Having Count(*) > 1
    Place it after the Group By.

    Also, if you just want to remove people with status deceased, can you not add " And pd.status <> 'deceased'" to your where clause?

    Always find it tricky to build up SQL in my head.. so much easier to write it when you have access to the underlying data

    Hope that helps

  3. Received thanks from:

    Raz316 (24-07-2008)

  4. #3
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Re: Mysql - MAX in WHERE section

    Haha wow thanks for the quick reply. That's exactly what I was after

    edit

    Also, if you just want to remove people with status deceased, can you not add " And pd.status <> 'deceased'" to your where clause?
    nah, I needed it to be aware of names that had at least one record flagged as 'deceased' so it knows to ignore all records of that name.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 36
    Last Post: 17-07-2008, 06:24 PM
  2. Asus p5n-e SLI full tips tweaks and overclock guide
    By kinnth in forum Help! Quick Relief From Tech Headaches
    Replies: 1
    Last Post: 22-11-2007, 05:02 PM
  3. MySQL extension for PHP 5.2.3 not working
    By Jerrythafast in forum Help! Quick Relief From Tech Headaches
    Replies: 18
    Last Post: 13-06-2007, 08:03 PM
  4. Max Rules
    By Trippledence in forum Networking and Broadband
    Replies: 0
    Last Post: 11-04-2006, 04:57 PM
  5. Making PHP and MySQL Talk?
    By Dav0s in forum Software
    Replies: 4
    Last Post: 18-09-2005, 10:53 PM

Posting Permissions

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