CODEWITHSUNDEEP
mysqlsqlwhere-clausemaxdate
nineinchtrent
nineinchtrent

Reputation: 1

Selecting MAX date with WHERE clause

I have a table named technical where can I place the WHERE clause for my search function?

I want to insert the WHERE t1.blklot LIKE '01/01' - this line will display only those blklot = 01/01

SELECT * 
FROM technical t1
JOIN (
    SELECT blklot, MAX(date_update) AS MAXDATE
    FROM technical
    GROUP BY blklot
) t2 ON t1.blklot = t2.blklot
AND t1.date_update = t2.MAXDATE
ORDER BY t1.blklot

Upvotes: 0

Views: 323

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try like this

SELECT * 
FROM technical t1
  JOIN (
      SELECT blklot, MAX(date_update) AS MAXDATE
      FROM technical
      GROUP BY blklot
      )t2 ON t1.blklot = t2.blklot
      AND t1.date_update = t2.MAXDATE
WHERE t1.blklot LIKE '01/01
ORDER BY t1.blklot

OR make a condion in ON CLAUSE

SELECT * 
FROM technical t1
  JOIN (
      SELECT blklot, MAX(date_update) AS MAXDATE
      FROM technical
      GROUP BY blklot
      )t2 ON t1.blklot = t2.blklot
      AND t1.date_update = t2.MAXDATE
      AND t1.blklot LIKE '01/01
ORDER BY t1.blklot

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You can actually place it either in the subquery or the outer query. In the subquery, you would need to change the table alias in the condition. Your condition is on the key used for aggregation, so it will work in either place.

Or, you could just put it in the on clause:

ON t1.blklot = t2.blklot and
   t1.date_update = t2.MAXDATE and
   t1.blklot LIKE '01/01'

EDIT:

Actually, put it in the subquery. That is the most efficient way:

SELECT * 
FROM technical t1 JOIN
     (SELECT blklot, MAX(date_update) AS MAXDATE
      FROM technical
      WHERE blklot LIKE '01/01'
     ) t2
     ON t1.blklot = t2.blklot AND
        t1.date_update = t2.MAXDATE
ORDER BY t1.blklot;

Because you are choosing only one value, the group by is not necessary.

Upvotes: 1

Related Questions