CODEWITHSUNDEEP
mysqlsqlaggregate-functions
avatar
avatar

Reputation: 12495

MySQL Min(), MAX() using the IDs from another table

I have two very simple MySQL tables: Table1 with a list of unique item_ids and the second one Table2 with records using the the item ids form the first table. Table2 contains also the date the record was made. To recap here is how the tables look like:

Table1 columns: item_id <---these are unique IDs

Table2 columns: item_id, item_title, time_stamp

What I need is a MySQL query that will give me a list with the date of the first and last record have been made for each item_id in Table2. It's imperative that Table2 will look just for the item_id in Table1 because Table2 contains item_ids that are not contained in Table1. So in other words I'm using Table1 just for sorting based on item_id. Your help is greatly appreciated.

Upvotes: 0

Views: 608

Answers (2)

lweller
lweller

Reputation: 11317

select item_id, maxts.ts, mints.ts
from (select item_id, max(time_stamp) as ts from Table2 group by item_id) maxts
join (select item_id, min(time_stamp) from Table2 group by item_id) mints on maxts.item_id=mints.item_id
join Table1.item_id t1 on t1.item_id=mints.item_id;

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425341

SELECT  t2.item_id, MIN(t2.time_stamp), MAX(t2.time_stamp)
FROM    table2 t2
JOIN    table1 t1
ON      t1.item_id = t2.item_id
GROUP BY
        t1.item_id
ORDER BY
        t1.ordering_column

In MySQL, you can use ungrouped and unaggregated expressions in SELECT and ORDER BY.

Upvotes: 3

Related Questions