SQL2005 Select Into - Remove Duplicates, Group By

I have this SQL 2005 table as shown with Duplicate Rows,

I wish to delete the duplicate rows, using Distinct CandidateID, Max(CreatedOn).
CandidateID should be the final Primary Key.

The Result Table should be the same as this SQL.
SELECT     CandidateID, MAX(CreatedOn) AS Expr2
FROM         db_Candidate
GROUP BY CandidateID


I think I should be changing from this code below, which does not give me Max(CreatedOn)
select * into a_Candidate
from (select  from db_Candidate) A

is this clear enough?

a.jpg
LVL 1
dennisdominicAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
to include all other columns, you need this.
SELECT t1.*
  INTO NewTable
  FROM db_Candidate t1
  JOIN (SELECT CandidateID, MAX(CreatedOn) AS CreatedOn FROM db_Candidate GROUP BY CandidateID) t2
    ON t1.CandidateID = t2.CandidateID AND t1.CreatedOn = t2.CreatedOn

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
SharathData EngineerCommented:

You already have the query. include INTO clause like this.
SELECT CandidateID, MAX(CreatedOn) AS CreatedOn
  INTO NewTable
  FROM db_Candidate
 GROUP BY CandidateID

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dennisdominicAuthor Commented:
Hi Sharath 123
SELECT CandidateID, MAX(CreatedOn) AS CreatedOn INTO NewTable FROM db_Candidate GROUP BY CandidateID
This is very close, but can I include all the other columns as well?
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT a.*
INTO NewTable
FROM db_Candidate a
INNER JOIN (

SELECT CandidateID, MAX(CreatedOn) AS CreatedOn
FROM db_Candidate
GROUP BY CandidateID
) b
On a.CandidateID = b.CandidateID AND A.CreatedOn =b.CreatedOn
0
 
SharathData EngineerCommented:
or this
SELECT t1.*
  INTO NewTable
  FROM db_Candidate t1
 WHERE CreatedOn = (SELECT MAX(CreatedOn) FROM db_Candidate  t2 WHERE t1.CandidateID = t2.CandidateID)

Open in new window

0
 
dennisdominicAuthor Commented:
both works but Sharath was faster.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.