Selecting MAX date with WHERE clause

Question

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

please mention technical table contain date field or "blklot = 01/01" from a varchar field — Anand Somasekhar, Feb 06 '14 at 15:39

score 1 · Answer 1 · answered Feb 01 '14 at 15:38

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.

score 0 · Answer 2 · answered Feb 01 '14 at 15:39

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

thank you Vignesh, but Mr Gordon's query works. I appreciate your response! thanks brother! :) — nineinchtrent, Feb 03 '14 at 00:49

Selecting MAX date with WHERE clause

2 Answers2