CODEWITHSUNDEEP
sqlgreatest-n-per-group
Rya
Rya

Reputation: 329

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

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.

Upvotes: 1

Views: 2565

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

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

Upvotes: 1

Egret
Egret

Reputation: 799

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

Upvotes: 2

Related Questions