select id with max date and keep all same max date SQL

Question

I have a sample dataset

id  category  date        value
1   a         2013-01-02  7
2   a         2013-01-02  2
3   a         2013-01-01  3
4   b         2013-01-01  1
5   b         2013-01-02  4
6   b         2013-01-03  5
7   c         2013-01-03  4
8   c         2013-01-03  8

I would like to return the following table as output

id  date        
1   2013-01-02  
2   2013-01-02  
6   2013-01-03  
7   2013-01-03  
8   2013-01-03  

I use the following code to get result,but date only return once. I would like to keep both.

SELECT id,date
FROM order t1
INNER JOIN 
(
   SELECT id, MAX(date) as maxdate
   FROM order
   GROUP BY category
) t2
ON t1.id = t2.id
AND t1.date = t2.maxdate

Please advice if I have something wrong.

Answer 1

From your example - you seem to want a query that gives you all the rows that match the max date in each category?

If so, you should group across the category (don't grab the ID from your t2). The subselect should give you the category and the maximum date, the outer correlated join will give you all the rows that match that category and date.

SELECT category,id,date
FROM order t1
INNER JOIN 
(
   SELECT category, MAX(date) as maxdate
   FROM order
   GROUP BY category
) t2
ON t1.category = t2.category
AND t1.date = t2.maxdate