Re: MIN(foo) as bar WHERE bar>50

Andrew Pattison Sat, 25 Sep 2004 10:35:32 -0700

MySQL supports sub-queries in the 4.1 versions.

Cheers

Andrew.

----- Original Message ----- From: "Andy Bakun" <[EMAIL PROTECTED]> To: "Laszlo Thoth" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, September 25, 2004 6:30 PM Subject: Re: MIN(foo) as bar WHERE bar>50

On Fri, 2004-09-24 at 21:31, Laszlo Thoth wrote:

Here's the kids:
mysql> SELECT p.name as parent,c.name as child,(TO_DAYS(NOW())-TO_DAYS(c.dob))/365 as age FROM people as p LEFT JOIN people as c ON p.name=c.parent WHERE c.name IS NOT NULL ORDER BY p.dob;


You seem to be missing a parent child relationship from your results for
some reason.  When I run your queries, I also get a 141 year old child:

| James Smith | Samuel P  | 141.07 |

mysql> SELECT p.name as parent,c.name as child,MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) as minage FROM people as p LEFT JOIN people as c ON p.name=c.parent WHERE c.name IS NOT NULL AND minage > 50 GROUP BY parent ORDER BY p.dob; ERROR 1054: Unknown column 'minage' in 'where clause'

Unfortunately it doesn't look like I can SELECT on the MIN() result: I can only specify which rows go into the MIN(). How can I perform this select?


Look up the HAVING clause in the mysql manual.  I believe HAVING is not
standard SQL, and is a MySQL specific addition.

  select p.name as parent,
         c.name as child,
         MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) as minage
    from people as p left join
         people as c on p.name = c.parent
   where c.name is not null
   group by parent
-> having minage > 50
   order by p.dob;

I personally try to avoid using HAVING on queries where the WHERE clause
would return a lot of rows because it needs to look at every row to see
if it satisfies the HAVING condition and this can be extremely
inefficient.  But it's useful and sometimes you can't avoid using it.
Your query could also be extremely inefficient because you need to apply
the formula to calculate minage to all rows.

HAVING is essentially like making a temporary table and then selecting
from that temporary table.  I could be considered a stop-gap method of
simple subqueries to tide us over until MySQL supports sub-queries
(sometime in the 5.x timeframe, I believe).

A more efficient query might be something like this pseudo-code:

  all rows where max(dob) < date_sub(now(), interval 50 years)

which should give you similar results... the query is going to look
something like this:

 select p.name as parent,
        max(c.dob) as dob_of_youngest_child
   from people p left join
        people c on p.name = c.parent
  where c.name is not null
  group by parent
 having dob_of_youngest_child < date_sub(now(), interval 50 year)

This may end up being more efficient because the values being applied to
max() are constant values (from the table, and are not the result of a
formula) and "date_sub(now(), interval 50 year)" is also a constant
(calculated at query parse time and not for every row, and optimized
away).

Use the EXPLAIN syntax to check for efficiency.

-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: MIN(foo) as bar WHERE bar>50

Reply via email to