Problem with Distinct query

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