Removing Duplicate Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Removing Duplicate Records

Author

Topic

prakashuj
Starting Member

12 Posts

Posted - 2007-12-05 : 06:51:22

Dear All

This is query i have written is giving following output.

FormCode RefCode Serialno DateTime
R1-196 H1-68 A1223213 8/6/2007 19:38:11
R1-196 H1-68 A1223213 8/6/2007 19:38:14
R1-205 H1-67 XS2312414 8/6/2007 19:36:08
R1-205 H1-67 XS2312414 8/6/2007 19:36:10
R1-220 H1-66 F43336534 8/6/2007 19:30:27
R1-220 H1-66 F43336534 8/6/2007 19:30:29
R1-400 H1-64 ER5343664 8/6/2007 19:24:23
R1-400 H1-64 ER5343664 8/6/2007 19:24:26
R1-408 H1-65 TE462626 8/6/2007 19:24:23
R1-408 H1-65 TE462626 8/6/2007 19:25:00

I want the output like this,it should take only Min Datecreated
FormCode RefCode Serialno DateTime
R1-196 H1-68 A1223213 8/6/2007 19:38:14
R1-205 H1-67 XS2312414 8/6/2007 19:36:08
R1-220 H1-66 F43336534 8/6/2007 19:30:27
R1-400 H1-64 ER5343664 8/6/2007 19:24:23
R1-408 H1-65 TE462626 8/6/2007 19:24:23

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 06:55:57

[code]DECLARE @Sample TABLE (FormCode VARCHAR(20), RefCode VARCHAR(20), SerialNo VARCHAR(20), dt DATETIME)

INSERT @Sample
SELECT 'R1-196', 'H1-68', 'A1223213', '8/6/2007 19:38:11' UNION ALL
SELECT 'R1-196', 'H1-68', 'A1223213', '8/6/2007 19:38:14' UNION ALL
SELECT 'R1-205', 'H1-67', 'XS2312414', '8/6/2007 19:36:08' UNION ALL
SELECT 'R1-205', 'H1-67', 'XS2312414', '8/6/2007 19:36:10' UNION ALL
SELECT 'R1-220', 'H1-66', 'F43336534', '8/6/2007 19:30:27' UNION ALL
SELECT 'R1-220', 'H1-66', 'F43336534', '8/6/2007 19:30:29' UNION ALL
SELECT 'R1-400', 'H1-64', 'ER5343664', '8/6/2007 19:24:23' UNION ALL
SELECT 'R1-400', 'H1-64', 'ER5343664', '8/6/2007 19:24:26' UNION ALL
SELECT 'R1-408', 'H1-65', 'TE462626', '8/6/2007 19:24:23' UNION ALL
SELECT 'R1-408', 'H1-65', 'TE462626', '8/6/2007 19:25:00'

SELECT FormCode,
RefCode,
SerialNo,
MIN(dt) AS DateTime
FROM @Sample
GROUP BY FormCode,
RefCode,
SerialNo[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 07:18:39

Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources