UPDATE TOP row for a group of rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

UPDATE TOP row for a group of rows?

Author

Topic

Jake_UK
Starting Member

7 Posts

Posted - 2008-01-06 : 10:32:03

My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.

I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.

SQL Server 2005

Table (many columns left out for simplicity)

ID - unique.
HdrID - key to the header record.
PTtimestamp - date, date/time the row was written to DB.
PType - integer, representing various states of the row.
etc
etc
etc (another 15 columns of data)....

Typically there is approximately 250 records per HdrID.

I am trying to do an SQL UPDATE without using my usual solution of writing vb code!

I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.

ie;

ID HdrID PTimestamp
001 001 01/01/2008 09:00:00
002 001 01/01/2008 09:10:00
003 001 01/01/2008 09:20:00
004 002 01/01/2008 09:00:00
005 002 01/01/2008 09:10:00
006 002 01/01/2008 09:20:00

In the above example I want to update the PType field = -9999 for record ID=003 & ID=006. In reality the table has tens of thousands of rows.

Jake

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-06 : 10:38:17

is this what you want ?


update t
set    PType = -999
from   table t
where  t.PTtimestamp = (select max(PTtimestamp) from table)

KH
[spoiler]Time is always against us[/spoiler]

Jake_UK
Starting Member

7 Posts

Posted - 2008-01-06 : 10:48:22

Blimey, that was a quick reply!

Sort of, but I was trying to set PType = -9999 for the maximum PTimestamp for each grouping of HdrID.

Each HdrID usually has 250+ rows with ascending PTimestamp values, the last one written for each HdrID I wanted to set PType to -9999.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-06 : 10:51:49

[code]UPDATE t
SET PType = -999
FROM TABLE t
INNER JOIN
(
SELECT HdrID, PTtimestamp = MAX(PTtimestamp)
FROM TABLE
GROUP BY HdrID
) m
ON t.HdrID = m.HdrID
AND t.PTtimestamp = m.PTtimestamp
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Jake_UK
Starting Member

7 Posts

Posted - 2008-01-06 : 11:10:12

Well I never, that works perfectly, many thanks indeed.

I really do need to learn SQL properly, I would have normally written a vb.net program to do this and similar tasks.

Again thanks, you saved me lots of time and head scratching :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-06 : 12:20:19

Another method:-

UPDATE t
SET t.PType =-9999
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY HdrID ORDER BY ID DESC)AS 'RowNo',
ID,
HdrID,
PTimestamp
FROM Table)t
WHERE t.RowNo=1

Jake_UK
Starting Member

7 Posts

Posted - 2008-01-06 : 13:34:50

Cheers visakh16. I had already done the updates but will be saving all these useful snippets of SQL :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources