SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update first row in groups, set rest to other val
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phrankbooth
Posting Yak Master

USA
160 Posts

Posted - 09/17/2013 :  13:54:25  Show Profile  Reply with Quote
I have this table:

ID| Type|Date
17494|NULL|2013-09-04 15:00:00.000

17577|NULL|2013-09-04 15:00:00.000

17639|NULL|2013-09-04 17:00:00.000

17701|NULL|2013-09-04 17:00:00.000

I'd like to Group by Date then Update the first row of each date group and set Type field to 'F' and the rest (could be one could be many) set those to 'V'

--PhB

Edited by - phrankbooth on 09/17/2013 13:56:34

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/17/2013 :  14:32:32  Show Profile  Reply with Quote
Here is one way. In the future, please post your data in a consumable format so we cna help you better/faster
DECLARE @Foo TABLE(ID INT, Type CHAR(1), [Date] DATETIME)
INSERT @Foo VALUES
(17494,NULL,'2013-09-04 15:00:00.000'), 
(17577,NULL,'2013-09-04 15:00:00.000'), 
(17639,NULL,'2013-09-04 17:00:00.000'), 
(17701,NULL,'2013-09-04 17:00:00.000')


UPDATE 
	A
SET 
	Type = CASE WHEN RowNum = 1 THEN 'F' ELSE 'V' END
FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY [Date]) AS RowNum
	FROM
		@Foo
) AS A

SELECT * FROM @Foo
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000