MySQL Help: SELECT table2.id WHERE ... table1.date is largest

Question

I have 2 tables, using an inner join to query them.

SELECT COUNT(table2.id) 
FROM table2 
INNER JOIN table1 ON table2.relazioneid = table1.id 
WHERE table1.date > ? AND table1.date < ?

It counts the ids of entries on reports between 2 dates. Table 1 holds info on the reports (date, groupid etc), table2 holds the entries on the reports.

I'd like to make a query almost exactly the same, except it only selects the ids from the report with the latest date, between those 2 dates.

Can anyone help? I can't quite get my head around MAX(date) type queries - all I get back is a count of every id and also the max date, rather than just those ids from the max date.

Thanks,

Answer 1

 ...WHERE table1.date BETWEEN'2009-01-01' AND '2009-01-02' ORDER BY table1.date LIMIT 10

will get you 10 first rows with the dates

Answer 2

I've been thinking perhaps a nested SQL query - can you do this? I can find references on Google, but can't get it to work... Something similar to:

SELECT COUNT(table2.id) 
FROM table2 
INNER JOIN table1 ON table2.relazioneid = table1.id 
WHERE table1.date > ? AND table1.date < ? AND 
(
    SELECT MAX(date) 
    FROM table1 
    WHERE date > ? AND date < ?
)

Can't get that to work though, and when I try to test it in phpMyAdmin, it kicks me out with an error telling me my user doesn't have SELECT privileges. Strange, since this is a test server I'm logged into as root.

Answer 3

Without further information regarding the structure of the tables, I would suggest that you try something like this:

SELECT t2.id
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.relation_id
WHERE t1.date BETWEEN ? AND ?
HAVING MAX(t1.date)

I haven't tested this but it should point in one direction you could go with this problem. Your other suggestion of using a subquery is valid and might work something like this:

SELECT *
FROM table2 t2 
WHERE t2.parent_id IN (
    SELECT t1.relation_id
    FROM table1 t1
    WHERE t1.date BETWEEN ? AND ?
    GROUP BY t1.relation_id
    HAVING MAX(t1.date)
)

Or in a join to a temporary table:

SELECT *
FROM table2 t2
INNER JOIN (
    SELECT t1.relation_id
    FROM table1 t1
    WHERE t1.date BETWEEN ? AND ?
    GROUP BY t1.relation_id
    HAVING MAX(t1.date)
) AS t1 ON t2.relation_id = t1.relation_id