SELECT query problem

Nate

I have a table like the following...

id | subID | LoggedDateTime
1    1           2004-12-03 12:03:01
1    2           2004-12-03 12:04:42
1    3           2004-12-03 12:04:49
2    1           2004-12-03 12:03:22
2    2           2004-12-03 12:03:44

I want to be able to select only rows where the highest subID of each id has a LoggedDateTime of say 2004-12-03 12:04:45 or less. But I need all rows of the id or none..

so the magical query would return:

id | subID | LoggedDateTime
2    1           2004-12-03 12:03:22
2    2           2004-12-03 12:03:44

not...

id | subID | LoggedDateTime
1    1           2004-12-03 12:03:01
1    2           2004-12-03 12:04:42
2    1           2004-12-03 12:03:22
2    2           2004-12-03 12:03:44

appreciate your help.
thanks

Nate

Anyone? any ideas? Forgot to mention im using MySQL.

barand

If you don't have subqueries (MySQL v.4.1) you can do it with a temp table

CREATE TEMPORARY TABLE tmp SELECT id, MAX(loggedDateTime) as maxtime FROM tablename GROUP BY id HAVING maxtime < '2004-12-03 12:04:45'

SELECT a.id, a.subid, a.loggedDateTime FROM tablename a
INNER JOIN tmp b ON a.id = b.id

hth

mtimdog

This probly won't work

select distinct id, subID, LoggedDateTime from tablename where maxtime < '2004-12-03 12:04:45' order by subID asc

or this

select id, min(subID), LoggedDateTime from tablename where maxtime < '2004-12-03 12:04:45' group by id asc