I took the liberty of making your sample data and table work correctly.
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')
Assuming this is correct you can easily use ROW_NUMBER for this.
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/