How to get common date using distinct or group by in sql server 2012

Question

I want to get common data using distinct or group by clause in sql server 2012 where my Createddate is the latest

below is the table i have

below is the query i have used to get the common TargetsDate2

select distinct convert(VARCHAR(15),TargetDate,106) as TargetDate2,TargetDate 
From LogsObservRequestActivity 
Where LogsObservRequestActivity.ActivityID=170 
and not TargetDate is null 
and isnull(TargetDate,'')<>isnull(TargetDateOriginal,'')

i am able to get common Targetdate2 but i want to order it by createdon desc, i have tried using order by clause but i do not get the expected result, i get all the dates if use order by clause

below is the expected output

Possible duplicate of [SQL Server: SELECT only the rows with MAX(DATE)](https://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate) — Ajay, May 15 '18 at 10:54

Yogesh Sharma · Answer 1 · 2018-05-15T10:32:09.407

You can use row_number() function, but i would use subquery :

select l.* 
from LogActivity l 
where TargetDate is not null and 
      LogActivityID = (select top 1 LogActivityID 
                       from LogActivity 
                       where ActivityID = l.ActivityID and
                             TargetDate2 = l.TargetDate2
                       order by CreatedOn DESC);

By, analytical function you can also achieve the same

select top (1) with ties, *
from LogActivity l 
where TargetDate is not null 
order by row_number() over (partition by ActivityID, TargetDate2 order by CreatedOn DESC);

score 0 · Answer 2 · answered May 15 '18 at 10:39

If You want only Highest createdOn column value for each Targetdate2 column, then use @Yogesh Sharma query

Else If you want all createdOn Column value for each Targetdate2 column then use this query

select distinct convert(VARCHAR(15),TargetDate,106) as TargetDate2,
       TargetDate 
       From LogsObservRequestActivity 
       Where LogsObservRequestActivity.ActivityID=170 
       and not TargetDate is null 
       and isnull(TargetDate,'')<>isnull(TargetDateOriginal,'') 
order by row_number() over (partition by ActivityID, TargetDate2 order by CreatedOn DESC);

DxTx · Answer 3 · 2018-05-15T10:51:38.883

Try this query...

SELECT sq.logactivityid, 
       sq.targetdate2, 
       t1.createdon, 
       t1.activityid 
FROM   tablename t1 
       INNER JOIN (SELECT Max(logactivityid) AS LogActivityID, 
                          targetdate2 
                   FROM   tablename 
                   GROUP  BY targetdate2) sq 
               ON t1.logactivityid = sq.logactivityid 
--WHERE  activityid = 170 
ORDER  BY t1.createdon

Note: This will not work as expected when logactivityid column has duplicate values. Therefore, please make sure logactivityid has unique values.

If I'm misunderstood, please let me know.

score 0 · Answer 4 · answered May 15 '18 at 10:53

You can write the query using window functions as:

select top (1) with ties convert(VARCHAR(15), TargetDate,106) as TargetDate2, TargetDate 
From LogsObservRequestActivity l
Where LogsObservRequestActivity.ActivityID = 170 and
      TargetDate is not null
      (TargetDate <> TargetDateOriginal and not (TargetDate is null and TargetDateOriginal is not null)
      )
order by row_number() over (partition by cast(TargetDate as date) order by TargetDate desc);

Notes:

The TOP (1) WITH TIES and ORDER BY ROW_NUMBER() negate the need for SELECT DISTINCT.
NOT <x> IS NULL is much less common than <X> IS NOT NULL.
I'm not a big fan of using ISNULL() or COALESCE() for the comparison you are making. For one thing, one argument is a string and one is a datetime. Do you even now if the the result is a string or datetime?
In databases other than SQL Server, the typical solution would be ROW_NUMBER() in a subquery.

How to get common date using distinct or group by in sql server 2012

4 Answers4