CODEWITHSUNDEEP
mysql
user3330441
user3330441

Reputation: 1

Cant get specific age to display

SELECT Name, doB, Age CURDATE( ) , (
YEAR( CURDATE( ) ) - YEAR( F )
) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( F, 5 ) ) AS Ages
SELECT Name, DoB, Ages WHERE Age=20 As Age
FROM Table_
ORDER BY N

Where do I put the the where AGES=20 if I want to only display the names of people that are 20 years old if the Age column is a temp column?

-- edit -- The oryginal query is:

SELECT N, F, CURDATE( ) , 
       ( YEAR( CURDATE( ) ) - YEAR( F ) ) 
       - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( F, 5 ) ) AS Age 
FROM edad_ LIMIT 0 , 30

Upvotes: 0

Views: 45

Answers (1)

spencer7593
spencer7593

Reputation: 108370

I can't make heads or tails of your query.

But to answer your question, a predicate on a derived column is not allowed in a WHERE clause, but it can be included in a HAVING clause. For example:

SELECT t.DoB
     , TIMESTAMPDIFF(YEAR, t.DoB, CURDATE()) AS age
  FROM mytable t
HAVING age = 20

BUT...

Be aware of performance issues. Note that the HAVING clause is applied very late in the processing of the query, after all of the rows have been retrieved and prepared. It acts more like a filter on the rows that are returned, while the WHERE clause acts as a filter on the rows retrieved, processed much earlier in the query.

For performance on large sets, we typically want to benefit from index range scans, so we use an equivalent predicate on a native column. (If a an appropriate index is available.)

For example, if an index on DoB is available:

SELECT t.DoB
     , TIMESTAMPDIFF(YEAR, t.DoB, CURDATE()) AS age
  FROM mytable t
 WHERE t.DoB >= CURDATE() + INTERVAL -21 YEAR
   AND t.DoB <  CURDATE() + INTERVAL -20 YEAR

(NOTE: You may need to replace >= with > and replace < with <= to get the age calculation working the way you want on the edge cases of CURDATE is exactly 20 years or exactly 21 years from DoB.)

Upvotes: 2

Related Questions