How get rows with max date from group?

Question

Table1    
Id, CatygoryId, Name, DataCreate

I want group this data by and get from group rows with max DataCreate

Example

Id   CatygoryId   Name   DataCreate

1       1         Name1  10/08/2016
2       1         Name2  11/08/2016
3       1         Name3  12/08/2016
4       1         Name4  13/08/2016
5       2         Name5  18/08/2016
6       2         Name6  19/08/2016

In result of query I want get

4       1         Name4  13/08/2016
6       2         Name6  19/08/2016

http://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate — Josh Part, Aug 19 '16 at 00:28
@Josh Part your link answered how get only rows with max date, but my for get rows from group row with max date — Hryhorii, Aug 20 '16 at 00:03
The answer to the question I linked to returns the row of each group with the max date for that group; if that's not what you need then you might need to rephrase your question. — Josh Part, Aug 20 '16 at 14:53

Liannis · Answer 1 · 2016-08-18T22:32:51.077

0

select max(Id), CatygoryId, max(Name), max(DataCreate) 
from Table1
group by CatygoryId

edited Aug 18 '16 at 22:32

answered Aug 18 '16 at 22:28

Liannis

111
6

That code I already tested before, but for name he returned different data. – Hryhorii Aug 19 '16 at 00:21
@Greg that is why I've suggested you to add what you have tried to your question – YakovL Aug 19 '16 at 09:05

score 0 · Answer 2 · answered Aug 19 '16 at 05:04

Try this

CREATE TABLE #TEMP_TABLE(ID INT, CatygoryId  INT, Name   VARCHAR(30), DataCreate DATE)

INSERT INTO #TEMP_TABLE

SELECT 1,       1,         'Name1',  '08/10/2016' UNION ALL
SELECT 2,       1,         'Name2',  '08/11/2016' UNION ALL
SELECT 3,       1,         'Name3',  '08/12/2016' UNION ALL
SELECT 4,       1,         'Name4',  '08/13/2016' UNION ALL
SELECT 5,       2,         'Name5',  '08/18/2016' UNION ALL
SELECT 6,       2,         'Name6',  '08/19/2016' 


;WITH T1
AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY CatygoryId ORDER BY DataCreate DESC) ROW_NO, * 
    FROM #TEMP_TABLE
)
SELECT *
FROM T1
WHERE T1.ROW_NO=1

score -1 · Answer 3 · edited Dec 12 '16 at 17:34

It looks like you want to group by ID. Also do you want the most recent date, or the top (2, 3, or etc.) records?

Btw, I think you may want to consider something like this, however will need to change date format to 'yyyymmdd' (easier to find max/most recent)

considering you have yyyymmdd format:

Table1
Id, CatygoryId, Name, DataCreate

Query:

Select Id, Name, CategoryId, DateCreated from Table1
Where DateCreate = (Select MAX(DateCreated) from Table1)
Group by Id

Note: this will give you all records with the most recent date, remember considering you have yyyymmdd format for year, which i recommend.

Now if you want more than just the first record. Consider this Query also using yyyymmdd format:

Select Id, Name, CategoryId, DateCreated from Table1
Where DateCreate = (Select top (# of rows you would like to return) Datecreated from Table1 

Order by DateCreated Desc)
Group by Id

How get rows with max date from group?

3 Answers3