Max date sql server

Question

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

You are misusing group by. Group by the items you have not aggregated on. For instance, an ID. — JonH, May 28 '14 at 19:44

score 3 · Answer 1 · answered May 28 '14 at 19:46

    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

score 1 · Accepted Answer · answered May 28 '14 at 19:50

1

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

answered May 28 '14 at 19:50

Dave.Gugg

6,561
3
24
43

How can I insert cte.[ID],cte.NAME,cte.dateverified, s.Dateinserted into a #temp table? I get "Column name or number of supplied values does not match table definition" error. create table #temp([ID] [varchar](10) NOT NULL,name [varchar](200) NOT NULL,[DateInserted] [datetime] NOT NULL,[DateVerified] [datetime] NOT NULL) – newbieCSharp May 28 '14 at 20:57
If you wanted to insert the data into a temp table, why didn't you include that in your question? Do you know the syntax for an insert with a cte? [See this.](http://stackoverflow.com/questions/3306096/combining-insert-into-and-with-cte) – Dave.Gugg May 28 '14 at 21:10

Max date sql server

2 Answers2