CODEWITHSUNDEEP
sql-server
newbieCSharp
newbieCSharp

Reputation: 191

Max date sql server

I have a table with data and I am trying to find max date verified

Create table staging(ID varchar(5) not null, Name varchar(200) not null, dateverified datetime not null,dateinserted datetime not null)
ID,Name,DateVerified,DateInserted
42851775,384,2014-05-24 08:48:20.000,2014-05-28 14:28:10.000
42851775,384,2014-05-28 13:13:07.000,2014-05-28 14:36:12.000
42851775,a1d,2014-05-28 09:17:22.000,2014-05-28 14:36:12.000
42851775,a1d,2014-05-28 09:17:22.000,2014-05-28 14:28:10.000
42851775,a1d,2014-05-28 09:17:22.000,2014-05-28 14:29:08.000
42851775,bc5,2014-05-28 09:17:21.000,2014-05-28 14:29:08.000
42851775,bc5,2014-05-28 09:17:21.000,2014-05-28 14:28:10.000
42851775,bc5,2014-05-28 09:17:21.000,2014-05-28 14:36:12.000

I want to display max dateverified for each keyid i.e.

42851775,384,2014-05-28 13:13:07.000,2014-05-28 14:36:12.000
42851775,a1d,2014-05-28 09:17:22.000,2014-05-28 14:36:12.000
42851775,bc5,2014-05-28 09:17:21.000,2014-05-28 14:29:08.000

 SELECT i.[ID],i.name,i.dateinserted,r.maxdate
    FROM (select id,name,max(dateverified) as maxdate from 
   [dbo].[staging] where id=42851775 group by id,name) r 
  inner join 
 [dbo].[staging] i 
  on r.id=i.id and r.jobpostingurl=i.jobpostingurl and r.maxdate=i.dateverified
   group by i.id,i.jobpostingurl,r.maxdate

I get an error,dateinserted is invalid as it is not contained in group by clause. But if I add it in group by clause I get all 8 records. How to handle this?

Thanks R

Upvotes: 0

Views: 148

Answers (2)

Dave.Gugg
Dave.Gugg

Reputation: 6781

If you need to get the dateinserted you can use a cte and join it back to the original table:

WITH    cte
          AS ( SELECT   [ID] ,
                        name ,
                        MAX(dateverified) AS dateverified
               FROM     [dbo].[staging]
               GROUP BY ID ,
                        name
             )
    SELECT  cte.[ID] ,
            cte.NAME ,
            cte.dateverified ,
            s.Dateinserted
    FROM    cte
            INNER JOIN dbo.staging s ON cte.[ID] = s.[ID]
                                        AND cte.NAME = s.NAME
                                        AND cte.dateverified = s.dateverified

Upvotes: 1

JonH
JonH

Reputation: 33183

    SELECT
        KeyID,
        MAX(yourDate)
   FROM
        Staging
    GROUP BY
        KeyID

If you want additional information join this to another table for instance:

SELECT
     b.KeyID,
     a.dateinserted,
     b.TheDate
FROM YourTable a
INNER JOIN
(
    SELECT
        KeyID,
        MAX(yourDate) AS TheDate
    FROM 
        Staging
    GROUP BY
        KeyID
) b
ON
b.KeyID = a.KeyID

Upvotes: 3

Related Questions