without using cursor - 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)

without using cursor

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2008-08-24 : 19:19:07

I have problem to write a query for the following conditions.
The below is the sample table....

TableA

Col1 Col2 Col3 Col4 Col5
AAA BBB CCC DDD 9999
AAA BBB CCC DDD 9999
AAA BBB CCC DDD 9999
www xxx yyy zzz 9999
www xxx yyy zzz 9999
www xxx yyy zzz 9999
PPP QQQ RRR SSS 9999
PPP QQQ RRR SSS 9999
PPP QQQ RRR SSS 9999
PPP QQQ RRR SSS 9999

In the above table i have 10 rows. In that first 3 rows, next 3 rows and next 4 rows contains same values.

So,what i need is, in col5 i have 9999 for all the rows. I dont want 9999 for all columns, i need the incremental value for the matched rows in col5, mean like i need value 1 instead of 9999 for the 1st 3 rows and for the next 3 rows value 2 and the for next 4 rows i need value 3...... Like that i need increment value by writing a query.

I need query without using Cursors

Please,Can any one help me out

Regards
SQLDEV

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-24 : 20:27:14

[code]
select Col1, Col2, Col3, Col4,
Col5 = dense_rank() over (order by Col1, Col2, Col3)
from TableA
[/code]

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-24 : 21:04:45

If you want to preserve your order for numbering it get's a little trickier. Dense_rank() will want to sort alpha by the column you choose rather then using the natural order of the table. Therefore the "PPP" field will be numbered before "WWW", causing the numbering to be differant then how you illustrated. A work around is below.


Declare  @Tmp Table(PK int identity(1,1),Col1 varchar(3),Col2 varchar(3),Col3 varchar(3),Col4 varchar(3),Col5 int)

Insert Into @Tmp(Col1,Col2,Col3,Col4)
Select Col1,col2,Col3,Col4
from 
(
select 'AAA' as col1, 'BBB' as col2, 'CCC' as col3, 'DDD' as col4, 9999 as col5 Union all 
select 'AAA', 'BBB', 'CCC', 'DDD', 9999 Union all
select 'AAA', 'BBB', 'CCC', 'DDD', 9999 Union all
select 'www', 'xxx', 'yyy', 'zzz', 9999 Union all
select 'www', 'xxx', 'yyy', 'zzz', 9999 Union all
select 'www', 'xxx', 'yyy', 'zzz', 9999 Union all
select 'PPP', 'QQQ', 'RRR', 'SSS', 9999 Union all
select 'PPP', 'QQQ', 'RRR', 'SSS', 9999 Union all 
select 'PPP', 'QQQ', 'RRR', 'SSS', 9999 Union all
select 'PPP', 'QQQ', 'RRR', 'SSS', 9999 
) a

Declare  @Tmp2 Table(PK int identity(1,1),Col1 varchar(3),Col2 varchar(3),Col3 varchar(3),Col4 varchar(3))
Insert Into @Tmp2(Col1,Col2,Col3,Col4)
select Col1,Col2,Col3,Col4
From
(
	select Min(PK) as mykey, Col1,Col2,col3,Col4
	from 
		@Tmp a
	group by  Col1,Col2,col3,Col4
) aa 
order by mykey


Select a.Col1,a.Col2,a.Col3,a.Col4,b.PK as Col5
from
	@Tmp a
inner Join
	@Tmp2 b
on		a.Col1 = b.Col1
and		a.Col2 = b.Col2
and		a.Col3 = b.Col3

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2008-08-27 : 09:54:10

Hi,

Dense_rank is good for me, but when i am updating the values into table using the dense_rank() its taking long time to update. i have some lakhs of records. daily records will increase more. its taking around 20 to 25 min to update into table.

Can you please help me out that.

quote:
Originally posted by khtan

select Col1, Col2, Col3, Col4,
Col5 = dense_rank() over (order by Col1, Col2, Col3)
from TableA

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