SQL WHERE + MAX query

Question

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?

The answer would be strongly specific to what DBMS are you using. — Barbaros Özhan
– Barbaros Özhan, Commented Feb 1, 2020 at 8:34

Gordon Linoff · Accepted Answer · 2020-02-01 00:17:04Z

1

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.

answered Feb 1, 2020 at 0:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Henning Koehler · Accepted Answer · 2020-02-01 00:22:33Z

0

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'

answered Feb 1, 2020 at 0:22

Henning Koehler

2,6672 gold badges20 silver badges22 bronze badges

Collectives™ on Stack Overflow

SQL WHERE + MAX query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related