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

Answer 2

If you are using sql-server-2012 and above you can also use this.

DECLARE @T TABLE (id INT,  category  VARCHAR(5), [date] date,        value int)
INSERT INTO @T VALUES

(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)

SELECT  id, [date] FROM (
    SELECT id, [date], RANK() OVER( PARTITION BY category order by [date] desc) RNK from @T
) AS t
WHERE RNK = 1

Result:

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

Answer 3

I would do this as by using subquery only :

select o.*
from order o
where date = (select max(o1.date) 
              from order o1 
              where o1.category = o.category
             );