How to change the value of one row per series in SQL

I get data that looks like this:

SELECT * FROM AA_CURRENT_DERIVED


MHSF_ID LAST_NAME  ADMIT_DATE  NEW_OR_EXISTING
------- ---------- ----------- ---------------
MHSF1   JONES      1           0
MHSF1   JONES      2           0
MHSF1   JONES      3           0
MHSF5   WU         1           0
MHSF5   WU         2           0

There are multiple rows per MHSF_ID value. For each MHSF_ID I want to change the value of NEW_OR_EXISTING to 1 for one of its rows, and keep the rest 0s.

For example,  update the above to this:

MHSF_ID LAST_NAME  ADMIT_DATE  NEW_OR_EXISTING
------- ---------- ----------- ---------------
MHSF1   JONES      1           0
MHSF1   JONES      2           0
MHSF1   JONES      3           1
MHSF5   WU         1           0
MHSF5   WU         2           1

In the example, I'm changing the value for the row with the greatest value of the ADMIT_DATE.
(The real data would have actual datetime values for admit date, not integers like shown here)

The data can be created with this script:

CREATE TABLE [dbo].[AA_CURRENT_DERIVED](
	[MHSF_ID] [char](5) NULL,
	[LAST_NAME] [varchar](50) NULL,
	[ADMIT_DATE] [int] NULL,
	[NEW_OR_EXISTING] [smallint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 1, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 2, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 3, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF5', N'WU', 1, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF5', N'WU', 2, 0)
GO

Open in new window

cipriano555Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
If you are going to match them based on the max(admit_date) then you can use this:

select SELECT a.MHSF_ID,a.LAST_NAME,a.ADMIT_DATE, case when a.ADMIT_DATE=b.ADMIT_DATE then 1 else 0 end as NEW_OR_EXISTING  
FROM AA_CURRENT_DERIVED a left join 
(select MHSF_ID, max(admit_date) as admit_Date from AA_CURRENT_DERIVED group by MHSF_ID) b on a.MHSF_ID=b.MHSF_ID

Open in new window


The code was prepared here : http://sqlfiddle.com/#!3/b86a3/2
0
John_VidmarCommented:
SELECT	a.MHSF_ID
,	a.LAST_NAME
,	a.ADMIT_DATE
,	NEW_OR_EXISTING	=	CASE a.RN WHEN 1 THEN 1 ELSE 0 END
FROM	(	SELECT	*
		,	RN	=	ROW_NUMBER() OVER
					(	PARTITION
						BY	MHSF_ID
						ORDER
						BY	ADMIT_DATE DESC
					) 
		FROM	AA_CURRENT_DERIVED
	) a

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.