-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Brian,
Have you tried something like : Select max(DepthFrom) 'DepthFrom', WellID, Easting, Northing, DepthTo from WellTableName group by WellID order by WellID If this doesn't work do you have a unique ID column? If you do then try this as the where condition of the SQL statement. Cheers Ian Cowper, Brian wrote: > Thanks to all who have responded. > > The solutions I have so far show me how to make the data in column DepthFrom descending or show overall statistics, but that's not what I need. > > To clarify - each Well has multiple rows, take for instance my example below, there are three rows of data for Well 0001, I want strip out the 2 rows that contain lower elevations and be left with row that has the highest DepthFrom value (250) and it is the same for all the other records, so with Well 0002, I would be left with row that has the 265 elevation, for Well 0003 an elevation of 239, etc. > > WellID Easting Northing DepthFrom Depthto > > 0001 50000 400000 250 240 > 0001 50000 400000 240 220 > 0001 50000 400000 220 210 > 0002 50050 400025 265 255 > 0002 50050 400025 255 230 > 0003 50070 400005 239 228 > 0003 50070 400005 228 213 > etc... > > What I need to do is extract from this table the records with the highest > DepthFrom value. So I would end up with this table after running my query: > > 0001 50000 400000 250 240 > 0002 50050 400025 265 255 > 0003 50070 400005 239 228 > > TIA > > Brian > > -----Original Message----- > From: Mcinnis, Michael [mailto:[EMAIL PROTECTED] > Sent: Monday, May 10, 2004 3:47 PM > To: Cowper, Brian > Subject: RE: MI-L Select Highest Value only in Table - Multiple Records > > > Something like this will work: > > Select Top 10 From Wells Order By Depthto desc > > > > -----Original Message----- > From: Cowper, Brian [mailto:[EMAIL PROTECTED] > Sent: Monday, May 10, 2004 12:26 PM > To: MapInfo-L (E-mail) > Subject: MI-L Select Highest Value only in Table - Multiple Records > > > I have a table containing thousands of point locations which represent Water > Well Locations, the Well in the following format: > > WellID Easting Northing DepthFrom Depthto > > 0001 50000 400000 250 240 > 0001 50000 400000 240 220 > 0001 50000 400000 220 210 > 0002 50050 400025 265 255 > 0002 50050 400025 255 230 > etc... > > What I need to do is extract from this table the records with the highest > DepthFrom value. So I would end up with this table after running my query: > > 0001 50000 400000 250 240 > 0002 50050 400025 265 255 > > Is there any easy way to achieve this using SQL select or select, or do I > have to create a piece of code for this? I should add that I'm very green > with MI 7.0 and haven't looked into MapBasic at all. > > > Regards, > > BChrisCo > - -- Ian Tidy GIS Administrator Works Asset Napier City Council mailto:[EMAIL PROTECTED] http://www.napier.govt.nz - ---------------------------- Public Key http://search.keyserver.net:11371/pks/lookup?op=get&[EMAIL PROTECTED] - ---------------------------- -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAn+bX6Fx3qA5Z0mgRAq//AJ4iJHjBUzJy1dONnrbZXrZm7Bvv/QCfa7hT FPIxJW4IZCxBF9dAuRuTp9M= =vDGN -----END PGP SIGNATURE----- ###################################################################### Attention: This e-mail message and accompanying data may contain information that is confidential and subject to legal privilege. Any information provided is given in good faith. However unless specifically stated to the contrary, Napier City Council accepts no liability for the content of this e-mail or for the consequences of any action taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that any use, dissemination, distribution or copying of this message or data is prohibited. If you received this e-mail message in error, please notify us immediately and erase all copies of this message and attachments. Thank you. --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11676
