Need 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with Query

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2010-05-11 : 18:18:42

Hi All,

Can anybody please help me out to build a query for the below shown scenario....

Here is the table which i have.....

Col1 Col2 Col3(Version)
1 Jan 1 2010 NULL
1 Jan 3 2010 NULL
1 Jan 10 2010 NULL
2 Jan 1 2010 NULL
2 Jan 20 2010 NULL
2 Jan 4 2010 NULL

Now i need to write a query to update the col3(Version) in such a way that, for the value of 1 in Col1, the Col3 needs to be updated with the version values based on Col2. So the output should look like

Col1 Col2 Col3(Version)
1 Jan 1 2010 1
1 Jan 3 2010 2
1 Jan 10 2010 3
2 Jan 1 2010 1
2 Jan 4 2010 2
2 Jan 20 2010 3

Really appreciate if someone can find a way to do this.

Thanks in Advance

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 18:34:40

[code];WITH CTE AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS Rank
FROM table_name)
UPDATE CTE
SET Col3 = Rank
[/code]

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-11 : 18:38:47

Would you specify the datatype of your columns (VARCHAR, DATETIME, ...) because this will affects the query.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-11 : 18:40:27

quote:

Originally posted by ms65g

;WITH CTE AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS Rank
FROM table_name)
UPDATE CTE
SET Col3 = Rank

This will not work if Col2 datatype is VARCHAR

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 18:58:43

quote:
Originally posted by malpashaa

quote:
Originally posted by ms65g

;WITH CTE AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS Rank
FROM table_name)
UPDATE CTE
SET Col3 = Rank

This will not work if Col2 datatype is VARCHAR

If Col2 datatype is varchar we can convert the column to datetime
Like this:

;WITH CTE AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY cast(col2 as datetime)) AS Rank
FROM @t)
UPDATE CTE
SET Col3 = Rank

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2010-05-11 : 19:27:30

Wow, that's really great. I just posted this one hour back and got the reply.Thanks ms65g and Pasha. I really appreciate the help which you guys did. Small request again(I am the beginner and have no knowledge on Queries :( ). I never used CTE and didn't understood a single step of your query( But it worked fine :)). Could you please explain me a little about CTE and how that works?

Thanks

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 19:41:36

Thanks, you can use derived table instead of CTE like this:

UPDATE D
SET Col3 = Rank
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS Rank
      FROM table_name) AS D

SQL Server doesn't limit the actions against table expressions (derived tables, CTEs, views, and inline table-valued UDFs) to SELECT only, but also allows other DML statements against those (INSERT, UPDATE, DELETE).

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2010-05-11 : 19:42:16

I should have posted before but just remembered about this....
Here is the scenario...
Now we updated the original table and next time when we insert some record, the record should get inserted in the table and the version should get updated too.
Now we got this...
Col1 Col2 Col3(Version)
1 Jan 1 2010 1
1 Jan 3 2010 2
1 Jan 10 2010 3
2 Jan 1 2010 1
2 Jan 4 2010 2
2 Jan 20 2010 3

When we insert a record with values (Col1 = 1, Col2 = Jan 6 2010) then the version should get updated accordingly and the table should look like this....

Col1 Col2 Col3(Version)
1 Jan 1 2010 1
1 Jan 3 2010 2
1 Jan 6 2010 3
1 Jan 10 2010 4
2 Jan 1 2010 1
2 Jan 4 2010 2
2 Jan 20 2010 3

This is being done in a transaction..so cannot do this manually and also 100's of records will be inserted and they needs to be updated accordingly...It would be great if someone can help me on this.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 19:47:58

You can calculate the values of columns 3 at executing query time, and do not consider col3 in physicals design
I mean:

SELECT *, col3 = ROW_NUMBER() OVER(....) FROM table

Also you can update the table every time that are rows inserted by using DML after insert trigger.

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2010-05-11 : 22:45:02

ok..i will check with this in the morning and let you know what i have decided to do in a transaction. Thanks for your help on this. Appreciate it.

Julien.Crawford
Starting Member

21 Posts

Posted - 2010-05-12 : 01:45:45

This might be a little old school, but this is how I usually do this type of thing.

// assuming clusted index on col1,col2 (if not you'll need a temp table with this index)

drop table t
go
create table t (col1 int not null, col2 datetime not null, col3 int null, primary key (col1,col2))
go
insert into t (col1,col2) select 1, '1 jan 2010'
insert into t (col1,col2) select 1, '3 jan 2010'
insert into t (col1,col2) select 1, '5 jan 2010'
insert into t (col1,col2) select 1, '8 jan 2010'
insert into t (col1,col2) select 2, '1 jan 2010'
insert into t (col1,col2) select 2, '4 jan 2010'
insert into t (col1,col2) select 2, '12 jan 2010'

select * from t

declare @n int
set @n = 0
declare @PrevCol1 int
set @PrevCol1 = 0

update t
set @n = case when @PrevCol1 = col1 then @n + 1 else 1 end
, col3 = case when @PrevCol1 = col1 then @n + 1 else 1 end
, @PrevCol1 = col1

select * from t

Julien.Crawford
Starting Member

21 Posts

Posted - 2010-05-12 : 01:58:19

I wrote this on sybase12.5 and it differs from sqlserver2005
Here is the ammended version
Note the initial value of 0 rather than 1 - Interesting.

drop table t
go
create table t (col1 int not null, col2 datetime not null, col3 int null, primary key (col1,col2))
go
insert into t (col1,col2) select 1, '1 jan 2010'
insert into t (col1,col2) select 1, '3 jan 2010'
insert into t (col1,col2) select 1, '5 jan 2010'
insert into t (col1,col2) select 1, '8 jan 2010'
insert into t (col1,col2) select 2, '1 jan 2010'
insert into t (col1,col2) select 2, '4 jan 2010'
insert into t (col1,col2) select 2, '12 jan 2010'

select * from t

declare @n int
set @n = 0
declare @PrevCol1 int
set @PrevCol1 = 0

update t
set @n = case when @PrevCol1 = col1 then @n + 1 else 0 end
, col3 = case when @PrevCol1 = col1 then @n + 1 else 0 end
, @PrevCol1 = col1

select * from t

Julien.Crawford
Starting Member

21 Posts

Posted - 2010-05-12 : 18:27:42

also, its a single pass - I'd be surprised (nicely) if there is any means of getting better performance.

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2010-05-12 : 21:53:53

Yeah even this one worked. Perfect...Thanks for your reply Julien.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources