May 25, 2006 at 10:36 am
Hello All,
Could you suggest me a simplier way to avoid cursors here as there are millions of rows and cursors would take horrible amount of time.
i want one acct_num for each group of da
example of results iam looking for is
da_id acct_num
22 12
23 30
24 50
2500 6666
/*code for the sample table*/
create table #temp1 (da_id int, acct_num int pk)
insert into #temp1 values ('22', '12')
insert into #temp1 values ('22', '21',)
insert into #temp1 values ('23', '30')
insert into #temp1 values ('23', '44')
insert into #temp1 values ('24', '50')
insert into #temp1 values ('2500', '6366666')
insert into #tehek values ('2500', '6666')
/* I tried this cursor but it would take lots of time for the huge data we have*/
declare @variable int
DECLARE RDRCursor CURSOR FAST_FORWARD
for select distinct da_id from #temp1
OPEN RDRCursor
FETCH NEXT FROM RDRCursor INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 (acct_num), da_id from #temp1 where da_id= @variable
FETCH NEXT FROM RDRCursor INTO @variable
end
close RDRCursor
deallocate RDRCursor
May 25, 2006 at 10:39 am
Read about SQL aggregates and GROUP BY:
SELECT da_id, MIN(acct_num) As LowestAcctNum
FROM #temp
GROUP BY da_id
May 25, 2006 at 10:41 am
May 25, 2006 at 10:42 am
May 25, 2006 at 11:41 am
Thanks that helped, But in the same lines could you suggest how to get one record per group if the other column is acct_name varchar instead of acct_num.
like
create table #temp1 (da_id int, acct_name varchar)
insert into #temp1 values ('22', 'aa12')
insert into #temp1 values ('22', 'bb21',)
insert into #temp1 values ('23', 'cc30')
insert into #temp1 values ('23', 'dd44')
insert into #temp1 values ('24', '5dd0')
insert into #temp1 values ('2500', 'ee6366666')
May 25, 2006 at 12:41 pm
MIN() and MAX() aggregates work with varchar, so the concept is no different.
May 26, 2006 at 1:59 am
The one difference that may arise if when we have numbers as strings, and we impose 'our' way of how to sort 'numerics strings'.
declare @x table ( v varchar(10) not null )
insert @x
select 'aa2' union all
select 'aa13' union all
select 'aa02'
select * from @x
order by v
v
----------
aa13
aa2
select max(v) as maxVal, min(v) as minVal from @x
maxVal minVal
---------- ----------
aa2 aa13
...may or may not be the 'right' sortorder, or min/max values.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply