CODEWITHSUNDEEP
mysqlsql
Lior
Lior

Reputation: 5674

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?

Upvotes: 1

Views: 579

Answers (5)

Jacob Eggers
Jacob Eggers

Reputation: 9332

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

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

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

Upvotes: 0

Blindy
Blindy

Reputation: 67487

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

Upvotes: 1

Brendan Bullen
Brendan Bullen

Reputation: 11819

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

Upvotes: 3

Naftali
Naftali

Reputation: 146350

This should work for what you need:

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

Upvotes: 1

Related Questions