query help - 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

query help

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2012-06-15 : 00:16:55

Dear All,

i have following table (i have sql server 2000)

create table tbl_data

(
rec_1 varchar(10),
rec_2 varchar(10),
Result char(1),
group_no int

)

following is the sample data set actual table contains more than 100000 records

insert into tbl_data (rec_1,rec_2,result) values ('A1','B1','Y')
insert into tbl_data (rec_1,rec_2,result) values ('A1','C1','Y')
insert into tbl_data (rec_1,rec_2,result) values ('A1','D1','N')
insert into tbl_data (rec_1,rec_2,result) values ('A1','F1','Y')

insert into tbl_data (rec_1,rec_2,result) values ('T1','B1','Y')
insert into tbl_data (rec_1,rec_2,result) values ('T1','C1','N')
insert into tbl_data (rec_1,rec_2,result) values ('T1','D1','N')
insert into tbl_data (rec_1,rec_2,result) values ('T1','F1','Y')

what i want to do is to assign same group_no to all matching rows on the basis of result column.

if result is 'Y' then same group_no should be assigned.

like

rec_1 rec_2 result group_no

'A1' 'B1' 'Y' 1
'A1' 'C1' 'Y' 1
'A1' 'F1' 'Y' 1
'T1' 'B1' 'Y' 2
'T1' 'F1' 'Y' 2

how can i update group no like this using sql query

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-15 : 01:44:31

Can you please try. maybe some other member will give you other option.


;With CTE
AS
(Select rec_1, rec_2, result from  #tbl_data where result='Y'
 Group by rec_1, rec_2, result
 )
 Select 
	rec_1, rec_2, result,
	case when rec_1='A1' Then 1
	 when rec_1='T1' Then 2
	End as GroupNo
	 from CTE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-15 : 02:16:28

[code]DECLARE @Sample TABLE
(
rec_1 varchar(10),
rec_2 varchar(10),
Result char(1),
group_no int
)

INSERT @Sample
(
rec_1,
rec_2,
Result
)
VALUES ('A1', 'B1', 'Y'),
('A1', 'C1', 'Y'),
('A1', 'D1', 'N'),
('A1', 'F1', 'Y'),
('T1', 'B1', 'Y'),
('T1', 'C1', 'N'),
('T1', 'D1', 'N'),
('T1', 'F1', 'Y')

-- Before
SELECT *
FROM @Sample

-- SwePeso
UPDATE f
SET Group_No = SeqNo
FROM (
SELECT Group_No,
DENSE_RANK() OVER (ORDER BY rec_1) AS SeqNo
FROM @Sample
WHERE Result = 'Y'
) AS f

-- After
SELECT *
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

gfaryd
Starting Member

27 Posts

Posted - 2012-06-15 : 04:25:56

i have mentioned that i m using sql server 2000 so i have no option of CTE and dense_rank() functions

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-15 : 04:32:14

[code]
update d
set group_no = (select count(distinct x.rec_1) from tbl_data x where x.rec_1 <= d.rec_1)
from tbl_data d
where result = 'Y'
[/code]

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

gfaryd
Starting Member

27 Posts

Posted - 2012-06-15 : 04:45:19

Thanx u all

gfaryd
Starting Member

27 Posts

Posted - 2012-06-15 : 05:20:40

update d
set group_no = (select count(distinct x.rec_1) from tbl_data x where x.rec_1 <= d.rec_1)
from tbl_data d
where result = 'Y'

can u explain (x.rec_1 <= d.rec_1) condition in the above query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources