ABR_ID	CERT_ID	Field_ID	certDate
31183	31996	DR	1987-12-13 00:00:00.000
31183	31997	SV	1995-02-28 00:00:00.000
31183	31998	SV	2004-07-16 00:00:00.000

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[jwmTestCert](
	[abr_ID] [nvarchar](10) NOT NULL,
	[cert_id] [int]  NOT NULL,
	[field_id] [nvarchar](3) NOT NULL,
	[certDate] [datetime] NOT NULL,
	[ExpireYear] [nvarchar](4) NULL,
	[status] [nvarchar](25) NOT NULL,
	[statusDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'dr', '1987-12-13')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31997,'sv', '1995-02-28')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'sv', '2004-07-16')

if object_id('jwmTestCert') is not null
	drop table jwmTestCert

CREATE TABLE [dbo].[jwmTestCert](
	[abr_ID] [nvarchar](10) NOT NULL,
	[cert_id] [int]  NOT NULL,
	[field_id] [nvarchar](3) NOT NULL,
	[certDate] [datetime] NOT NULL,
	[ExpireYear] [nvarchar](4) NULL,
	[status] [nvarchar](25) NULL,
	[statusDate] [datetime] NULL
) ON [PRIMARY]

GO


insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'dr', '1987-12-13')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31997,'sv', '1995-02-28')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31998,'sv', '2004-07-16')

select *, case when RowNum = 1 then 'Original' else 'Recertification' end
from
(
	select *, ROW_NUMBER() OVER (partition by field_id Order by certDate) as RowNum from jwmTestCert
) x