Help with query - SQL Server Forums

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

Help with query

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-02 : 08:33:02

I have a table testTable that i want to update the column status. The input to this column should be that the latest createDate should have "current" and everyone else "history" for every row group by

Only one should have 'current' in the column status for every unique id_fk.

Expected output:
1, 1, 'test1', '2010-01-01', 'history'
2, 2, 'test2', '2010-01-04', 'current'
3, 1, 'test1', '2010-02-01', 'current'
4, 1, 'test1', '2010-01-12', 'history'
5, 3, 'test3', '2010-01-21', 'current'

create table testTable(
id1 int
,id_fk int
,col1 varchar(10)
,createDate datetime
,status varchar(10)
primary key(id1))

insert into testTable(id1, id_fk, col1, createDate) values(1, 1, 'test1', '2010-01-01')
insert into testTable(id1, id_fk, col1, createDate) values(2, 2, 'test2', '2010-01-04')
insert into testTable(id1, id_fk, col1, createDate) values(3, 1, 'test1', '2010-02-01')
insert into testTable(id1, id_fk, col1, createDate) values(4, 1, 'test1', '2010-01-12')
insert into testTable(id1, id_fk, col1, createDate) values(5, 3, 'test3', '2010-01-21')

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-02-02 : 08:37:18

select id1,id_fk,col1,createdate, case when rid = 1 then 'current' else 'history' end as status
from (select *,row_number()over(partition by id_fk order by createdate desc)as rid from testtable)s

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-02 : 09:17:00

Thanks for the fast reply!
It seems to work, but how do I update the rows using a update query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 09:23:44

[code]update t
set t.status=case when rid = 1 then 'current' else 'history' end
from
(
select *,row_number()over(partition by id_fk order by createdate desc)as rid from testtable
)t
[/code]

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-03 : 04:38:10

I'm having difficulties understanding the query, can someone please explain the "row_number()over(partition by id_fk order by createdate desc)"?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 06:16:35

quote:
Originally posted by maevr

I'm having difficulties understanding the query, can someone please explain the "row_number()over(partition by id_fk order by createdate desc)"?

it basically divides your data into groups based on value of id_fk and then assigns sequential numbers based on decreasing order of created date. then you update all having sequential number > 1 as history and with 1 as current as it will most recent one

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources