Select Column Based on Maximum Value in Another Column

Let's assume there's a table called test, with two columns - id and time开发者_开发知识库. The id column is not unique.

I'd like to get all of the ids where there aren't any rows with that id with a time higher than X.

How can it be done?

---

SELECT id FROM test
GROUP BY id
HAVING MAX(time) <= 'X'

---

This should work for what you need:

SELECT id 
FROM tableName 
WHERE time > '{X}' AND NOT ID = '{ID}'
GROUP BY id;

---

select distinct id
from table t
where t.id not in (select it.id
                   from table it
                   where it.time>@x)

---

SELECT t.id 
FROM table t
WHERE NOT EXISTS
  ( SELECT * 
    FROM table tt
    WHERE tt.time > X
      AND tt.id = t.id
  )

---

SELECT id FROM (
    SELECT max(time) as max_time, id
    FROM test
    GROUP BY id
) tmp
WHERE max_time < 'X'