group by

Question

Post reply

group by

sqlpro

Hall of Fame

Points: 3859
More actions
July 18, 2005 at 9:13 pm

#186695

Hi friends
i need ur advise on this query.I've 2 fields in a table (patient,invoice).
field invoice contains invoice no for a patient.this no can be duplicated for the same patient but not for the different patient.
i mean we can have data like this
patient invoice
pk patient invoice
1    John    I001
2    John    I001
3    Paul     I002
but there should not be like following
pk patient invoice
1    John    I001
2    John    I001
3    Paul     I001 --its wrong
4   Paul     I002
my question is how can i find out such duplicates ?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ND-215452 SSCrazy Points: 2037 More actions · Answer 1

something like this?

select distinct a.patient, a.invoice, b.patient, b.invoice

from table a

, table b

where a.patient <> b.patient

and a.invoice = b.invoice

and a.pk < b.pk

order by a.invoice

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

Hi dude

thanks for the post.

The field pk is not a number it may contain char also

ND-215452 SSCrazy Points: 2037 More actions · Answer 3

It shouldn't make any difference as long as pk is unique I think!!

did it work??

noeld SSC Guru Points: 96590 More actions · Answer 4

select distinct a.patient, a.invoice, b.patient, b.invoice

from table a

, table b

where a.patient <> b.patient -- Make sure this can not be null

and a.invoice = b.invoice

and a.pk <> b.pk -- Minor change

order by a.invoice

* Noel

ND-215452 SSCrazy Points: 2037 More actions · Answer 5

and a.pk <> b.pk -- Minor change

This won't work, it does not return unique duplicates.

sqlpro Hall of Fame Points: 3859 More actions · Answer 6

Thank u very much Journeyman and Noel .

finally i got it sorted it in following way

SELECT patient, invoice FROM Patients PInvoiceWHERE PInvoice.Invoice IN

(SELECT Invoice FROM Patients PDup WHERE PDup.Patient # PInvoice.Patient)

GROUP BY PInvoice.patient, PInvoice.invoice

By the way Journeyman am running this query (i did not mention before) on

visual foxpro data and ur query hangs my PC for some reason but abv query is fine!!

Anyway i really appreciate ur help and it gave different way of looking at problem

and find solution.Thanks

ND-215452 SSCrazy Points: 2037 More actions · Answer 7

As you can see I never work with foxpro before

cheers

Suresh-209155 SSC-Addicted Points: 415 More actions · Answer 8

Try this,

Select Patient,invoice count(*)

From TablepateientInvoice

Group by Patient,invoice

Having count(*) > 1

sqlpro Hall of Fame Points: 3859 More actions · Answer 9

Thanks suresh.

ur query also worked nicely.in this case it also returns records which are not duplicated across patients.

what i mean ,say i've 10 records with same invoice no "I1" for same patient and no other patient has this no still abv query returns that invoice no in result.

anyway thats for ur time.