Saving the count query and the last step was what I was missing and it worked fine. I thought it wouldn't take so many steps and having to save query results, etc., but better more steps with it being able to be done, than it can't do it. Thanks to all -Bob
"Spencer Simpson" <[EMAIL PROTECTED]> writes: >Assuming you've done the following (which it sounds like you've done): > >1. Update the lon and lat columns with centroidX and centroidY >2. Make the group-by query (group by lat and lon. >3. Make the query where count>1 > >You should then: > >3. Save the result of query 2 to its own table (we'll call it >DUPLOCS.TAB). >4. Close the query tables generated in steps 1 and 2, then open the table >you saved in step 3 (DUPLOCS). >5. If the original table has 10000 records or more, index its lat column, >and the lat column of DUPLOCS. >6. Perform a SQL Select: > >select * from ORIGTAB, DUPLOCS where (origtab.lon=duplocs.lon) and >(origtab.lat=duplocs.lat) > > >Viola'! > >Hope this helps >Spencer > > >-----Original Message----- >From: Robert DeRubeis [mailto:[EMAIL PROTECTED] >Sent: Monday, May 17, 2004 12:02 PM >To: [EMAIL PROTECTED] >Cc: Kir Luong; Peter Horsb�ll M�ller >Subject: Re: MI-L points with same lat/lon > >I've done this, and found that I have 100's of duplications of 2 or more, >but I need all of the records that are duplicates. When a count query >is done it does not select all records with matching lat/lon's. It >returns, for example, >Count Lat Lon >6 49.715709 -123.156264 >5 49.709248 -124.916418 >4 43.745795 -79.526276 >etc, etc.. > >There are 15 records above with the same lat/lon. I need to be able to >select all 15 records? >-Bob > >"Kir Luong" <[EMAIL PROTECTED]> writes: >>If the lat or long is the same then a count and group by function on >>those variables should give you the records that are identical (group by >>clause) and how many duplication (count function): >>I'm not sure the mapinfo SqL...but something like: >> >>Select count(*),lat >>from table1 >>group by lat >>having count(*) > 1; >>execute; ===> returns a table of all lats that are duplicated and the >>number of duplication >> >>likewise for the longs. Hope this helps. >> >>kir. >> >>-----Original Message----- >>From: Robert DeRubeis [mailto:[EMAIL PROTECTED] >>Sent: Friday, May 14, 2004 3:03 PM >>To: [EMAIL PROTECTED] >>Subject: MI-L points with same lat/lon >> >> >>List- >>Is there a way to select all records from one table where the values in 2 >>different columns are identical? I wanted to select all records where the >>lat of 1st record = lat of 2nd record AND lon of 1st record = lon of 2nd >>record. I'm trying to identify where and how many points are right on >top >>of each other and if they need to be dispersed or better geocoding. >> >>The table has about 30,000 points and I'm using MIPro 6.5. >>Thanks, >>-Bob >> >> >>--------------------------------------------------------------------- >>List hosting provided by Directions Magazine | www.directionsmag.com | >>To unsubscribe, e-mail: [EMAIL PROTECTED] >>For additional commands, e-mail: [EMAIL PROTECTED] >>Message number: 11767 >> >> >>This communication is intended for the use of the recipient to which it >>is addressed, and may contain confidential, personal and or privileged >>information. Please contact us immediately if you are not the intended >>recipient of this communication, and do not copy, distribute, or take >>action relying on it. Any communication received in error, or subsequent >>reply, should be deleted or destroyed > > >--------------------------------------------------------------------- >List hosting provided by Directions Magazine | www.directionsmag.com | >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >Message number: 11790 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11794
