Specific group by latest date in SQL Server

Question

I want to run a SQL query on the following set of data

date creation_date value ------------------------------------ 2018-01-01 2017-05-05 1 2018-01-02 2017-05-05 0 2018-01-03 2017-05-05 1 2018-01-04 2017-05-05 0 2018-01-05 2017-05-05 1 2018-01-06 2017-05-05 1 2018-01-02 2017-05-11 5

I want to get the following results

2018-01-01 2017-05-05 1 2018-01-02 2017-05-11 5 2018-01-03 2017-05-05 1 2018-01-04 2017-05-05 0 2018-01-05 2017-05-05 1 2018-01-06 2017-05-05 1

Basically I want to get all the dates, group it by dates and get the latest creation date and get the value for that creation date.

I tried

select date, max(Creation_date), value from datasource group by date, blocked

but that doesn't do it.

what if there are many rows containing the latest Creation_Date? — Hadi
– Hadi, Commented Nov 15, 2017 at 10:29
@Hadi Many rows contaning the latest Creation_Date? Please explain... — mko
– mko, Commented Nov 15, 2017 at 10:40

shA.t · Accepted Answer · 2017-11-15 10:23:16Z

2

I think you can use query like this:

select * from ( select * , row_number() over (partition by date, blocked order by creation_date desc) seq from datasource) t where t.seq = 1;

SQL Server Fiddle Demo

answered Nov 15, 2017 at 10:23

shA.t

17k5 gold badges59 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hadi Over a year ago

if there are many rows containing the latest Creation_Date, this will choose one row randomly. But according to the OP informations provided, this solves the issue

shA.t Over a year ago

@Hadi if you want all of them you can use rank() function instead ;).

Sergey Menshov · Accepted Answer · 2017-11-15 10:28:15Z

1

Try to use subquery and join with it

SELECT d.* FROM datasource d JOIN ( SELECT [date],MAX(Creation_date) LastCreationDate FROM datasource GROUP BY [date] ) l ON d.[date]=l.[date] AND d.Creation_date=l.LastCreationDate

Or the second variant if Creation_date is common for all the [date]

SELECT * FROM datasource WHERE Creation_date=(SELECT MAX(Creation_date) FROM datasource)

edited Nov 15, 2017 at 10:28

answered Nov 15, 2017 at 10:22

Sergey Menshov

3,9062 gold badges11 silver badges22 bronze badges

5 Comments

Hadi Over a year ago

the second query will throw an exception if the are many dates = Max(Creation_Date)

Sergey Menshov Over a year ago

@Hadi - It isn't possible. I use aggregate function MAX without GROUP BY - the subquery returns only one row.

Hadi Over a year ago

SELECT * FROM datasource WHERE Creation_date = .... if there are Many rows that contains the latest creation date, this will not return one row for each date, which is required by the OP

Sergey Menshov Over a year ago

@mko - What about the first query? I wrote "Or the second variant if Creation_date is common for all the [date]".

mko Over a year ago

@Leran2002 first query is fine

Collectives™ on Stack Overflow

Specific group by latest date in SQL Server

2 Answers 2

2 Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Related