Hi,
 I have a table(claims) with 10-15 clumns...like
 claimno,serviceno,memno,serviceaff,ymdeff,ymdend....
 I want to select distinct rows only for the claimno,serviceno,memno....non
 distinct for rest of the clumns....I have a inner join on another
 table(Members with unique memno) on memno.
 If I say ( Select distinct
 claimno,serviceno,memno,serviceaff,ymdeff,ymdend )...It will select
 duplicate rows with claimno,serviceno,memno...I want unique
 claimno,serviceno,memno and only corresponding columns for the rest ( like
 serviceaff,ymdeff,ymdend.)
 Select Distinct
 CL.memno,Cl.serviceno,CL.memno,CL.serviceaff,CL.ymdend,CL.ymdeff.........
 ...
 from claims CL inner join members MM
 on CL.memno = MM.memno
 Thanks for the Help !!!Veena,
I don't think your goal is achievable.
For example, you have the following data:
claimno serviceno memno serviceaff ymdeff ymdend
1 1 1 a b
c
1 1 1 a b
b
1 1 2 a b
c
you have the distinct set of claimno,serviceno,memno:
1,1,1
1,1,2
however, which serviceaff,ymdeff,ymdend set is the ONE CORRESPONDING set for
the distinct claimno,serviceno,memno set of 1,1,1? There is no ONE defined
corresponding set. Both a,b,c and a,b,b qualify as corresponding set.
In case you want to have ANY ONE corresponding set of additional columns
selected together with the distinct sets of the first few columns, you can
use the following technique which is for eliminating duplicates:
delete from emp
where rowid in (select rowid
 from emp e
 where e.rowid > (select min(x.rowid)
 from emp x
 where x.emp_no = e.emp_no)
 )
hope this helps.
Quentin
"veena" <vgs@.yahoo.com> wrote in message
news:#76WtipSDHA.3768@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table(claims) with 10-15 clumns...like
> claimno,serviceno,memno,serviceaff,ymdeff,ymdend....
> I want to select distinct rows only for the
claimno,serviceno,memno....non
> distinct for rest of the clumns....I have a inner join on another
> table(Members with unique memno) on memno.
> If I say ( Select distinct
> claimno,serviceno,memno,serviceaff,ymdeff,ymdend )...It will select
> duplicate rows with claimno,serviceno,memno...I want unique
> claimno,serviceno,memno and only corresponding columns for the rest ( like
> serviceaff,ymdeff,ymdend.)
> Select Distinct
>
CL.memno,Cl.serviceno,CL.memno,CL.serviceaff,CL.ymdend,CL.ymdeff.........
> ...
> from claims CL inner join members MM
> on CL.memno = MM.memno
> Thanks for the Help !!!
>|||Hi,
I will modify little bit the table
claimno serviceno memno serviceaff ymdeff ymdend
1 1 1 a b
c
1 1 1 a d
c
1 1 2 a b
c
2 1 2 a b
c
Here I want to select claimno, serviceno,memno 111,112,212 and
corresponding set of columns abc ,abc,abc .. Since,In my destination table,
I have to define claomno,serviceno as primary key, It says Key
violation...so, I want to reject 111 with adc row.
I hope this is possible....
Thanks|||No this is NOT possible since SQL Server does not have an idea whether you
want to have 1,1,1,a,b,c or 1,1,1,a,d,c. You have to specify it. You can
generate a row counter value by inserting the data into a new table which
has an identity key, and then modify the logic I attached in the original
response to filter out the duplicates (of the "key" columns).
"veena" <vgs@.yahoo.com> wrote in message
news:OJpoJkvSDHA.1912@.tk2msftngp13.phx.gbl...
> Hi,
> I will modify little bit the table
> claimno serviceno memno serviceaff ymdeff ymdend
> 1 1 1 a b
> c
> 1 1 1 a d
> c
> 1 1 2 a b
> c
> 2 1 2 a b
> c
> Here I want to select claimno, serviceno,memno 111,112,212 and
> corresponding set of columns abc ,abc,abc .. Since,In my destination
table,
> I have to define claomno,serviceno as primary key, It says Key
> violation...so, I want to reject 111 with adc row.
> I hope this is possible....
> Thanks
>
>
>
Wednesday, March 7, 2012
Subscribe to:
Post Comments (Atom)
 
No comments:
Post a Comment