CODEWITHSUNDEEP
sqlgreatest-n-per-group
Jalu
Jalu

Reputation: 41

SQL WHERE + MAX query

I am trying to get a row from SQL db, which meets 2 conditions :

1) Value 1 == 'A'

AND

2) date is MAX

----------------------------------------------
|                 Log                        |
----------------------------------------------
|  date   |     value 1     |     Value 2    |
----------------------------------------------
|  1000   |        A        |       C        |
|  1100   |        A        |       C        |
|  1200   |        B        |       C        |
----------------------------------------------

I can get the max value with following query :

SELECT *
FROM log
INNER JOIN(
   SELECT MAX(date) date 
   FROM log
)MaxDate
ON MaxDate.date = log.date

How can i add 1st condition to this query?

Upvotes: 0

Views: 66

Answers (2)

Henning Koehler
Henning Koehler

Reputation: 2637

Assuming you want the row where the date is max among those rows with value1 = 'A', the following should work across all DBMSs and be easy to tweak:

SELECT *
FROM log
INNER JOIN(
   SELECT value1, MAX(date) date 
   FROM log
   GROUP BY value1
) MaxDate
ON MaxDate.date = log.date and MaxDate.value1 = log.value1
WHERE log.value1 = 'A'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use order by and limit to one row:

select l.*
from logs l
where l.value = 'A'
order by l.date desc
fetch first 1 row only;

Not all databases support fetch first. It might be limit or top (1), for instance.

Upvotes: 1

Related Questions