Select and grouping varchar column
-
Hello,
I have a table with 3 varchar column and an empty int coulmn.
This table contains duplicate rows and i want to fill the int column with a unique id that is the same for each group of identical rows.
Any Idea ?
Thanx
-
You could create another table with an identity column and do a "select distinct..." into it...
**ASCII stupid question, get a stupid ANSI !!!** -
off the cuff:
set rowcount 1
select @i = col1, @j-2 = col2, @k = col3
from mytable
where intcol is null
if exists( select intcol from mytable
where @i = col1 and @j-2 = col2 and @k = col3
and intcol is not null)
update mytable
set intcol = z.intcol
from mytable z
where @i = col1 and @j-2 = col2 and @k = col3
and intcol is not null
put in a loop while exists(select* where intcol is null)
-
Hi jonas,
Here's a simple implementation of sushila's suggestion...
--data
declare @t table (a varchar(1), b varchar(1), c varchar(1), i int)
insert @t (a, b, c)
select 'x', 'x', 'x'
union all select 'x', 'x', 'x'
union all select 'x', 'x', 'y'
union all select 'x', 'y', 'y'
union all select 'x', 'y', 'y'
union all select 'x', 'y', 'y'
union all select 'y', 'y', 'y'
--calculation
declare @U table (i int identity(1, 1), a varchar(1), b varchar(1), c varchar(1))
insert @U select distinct a, b, c from @t
update t set i = u.i from @t t inner join @U u on t.a = u.a and t.b = u.b and t.c = u.c
--results
select * from @t
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Or this - not tested this so probably full of errors, but you get the idea. Cursor probably not strictly necessary. As it's a one-off update I don't think the performance issue with cursors arises in this instance.
John
-- x, y and z below should reflect the column data types in your table
declare @table table(tempint int Identity(1,1), tempcol1 varchar(x), tempcol2 varchar(y), tempcol3 varchar(z))
insert into @table (select distinct col1, col2, col3 from YourTable)
declare @tempint int
declare @tempcol1 varchar(x)
declare @tempcol2 varchar(y)
declare @tempcol3 varchar(z)
declare tab_cur cursor for
select tempint, tempcol1, tempcol2, tempcol3 from @table
fetch next from tab_cur into @tempint, @tempcol1, @tempcol2, @tempcol3
while @@fetch_status = 0
begin
update YourTable set intcol = @tempint
where col1 = @tempcol1 and col2 = @tempcol2 and col3 = @tempcol3
fetch next from tab_cur into @tempint, @tempcol1, @tempcol2, @tempcol3
end
close tab_cur
deallocate tab_cur
-
Thanx all for your comments ,
but it doens't work.
I suspect some problems with the equalities test (i.e: Col1=a Col2=B etc) in all the queries involving such test. While it should normally return a true value when the test is valid , sql2kserver doesn't find any row matching this conditions. As the columns are of a varchar type, I tought of a collation problem or a case sensitivity problem and corrected the queries but the result is still the same.
The Columns doesn't match so the rows,
jk
-
Well, that's as clear as mud to me!
My solution works against the example data I provided (at least I think it does - do you think it does?), so maybe your data is different somehow?
Please try to find a sample of your data which doesn't work for my solution and post that data here.
Then we'll fix it for you...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Ok here is the code:
Table Cust definition:
Cust_Key Varchar(50) null,
Cust_CompanyName Varchar(50) not null,
Cust_CompanyAdress Varchar(255) null,
Cust_GroupID int null,
the table cust contains duplicate rows.
The code below is what i use for extracting matching IDs. Unfortunately , the last query does not return any value while it should. because what it exist in table Customers comes from Cust.
Any Idea ?
Thanx.
Code:
alter table Cust add ID int identity(1,1) not null
SELECT Cust_Key,Cust_CompanyName, Cust_CompanyAdress
into Customers FROM Cust
GROUP BY Cust_Key, Cust_CompanyName, Cust_CompanyAdress,
HAVING COUNT(*) >= 1
gO
alter table Customers add CustID int identity(1,1) not null
select Cust.ID, Customers.CustID
from Cust inner join on
Cust.Cust_Key=Customers.Cust_Key AND
Cust.Cust_CompanyName= Customers.Cust_CompanyName AND
Cust.Cust_CompanyAdress= Customers.Cust_CompanyAdress
-
Interesting. I ask for data. You give me code.
Well, never mind. I've corrected the syntax errors in your query, and added my sample data, and it all works as expected for me. Run the code below to see...
I don't know how I can help you further.
create table #Cust (Cust_Key varchar(50) null, Cust_CompanyName varchar(50) not null, Cust_CompanyAdress varchar(255) null, Cust_GroupID int null)
insert #Cust (Cust_Key, Cust_CompanyName, Cust_CompanyAdress)
select 'x', 'x', 'x'
union all select 'x', 'x', 'x'
union all select 'x', 'x', 'y'
union all select 'x', 'y', 'y'
union all select 'x', 'y', 'y'
union all select 'x', 'y', 'y'
union all select 'y', 'y', 'y'
alter table #Cust add ID int identity(1,1) not null
SELECT Cust_Key,Cust_CompanyName, Cust_CompanyAdress
into #Customers FROM #Cust Cust
GROUP BY Cust_Key, Cust_CompanyName, Cust_CompanyAdress
HAVING COUNT(*) >= 1
go
alter table #Customers add CustID int identity(1,1) not null
go
select Cust.ID, Customers.CustID
from #Cust Cust inner join #Customers Customers on
Cust.Cust_Key=Customers.Cust_Key AND
Cust.Cust_CompanyName= Customers.Cust_CompanyName AND
Cust.Cust_CompanyAdress= Customers.Cust_CompanyAdress
drop table #Cust
drop table #Customers
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Data's definition is more important than the data itself.
can you tell me what was wrong ( i haven't tested your code yet) . I can't see why you turned these two table into temp tables?
j
-
<Sigh>
I always try to use temporary objects so anyone (including me) can run the code without it messing up their database.
And all I did was correct syntax errors (e.g. "Cust_CompanyAdress,
HAVING"). I'm sure you can compare the 2 statements to see the trivial changes.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply