Herein lies Susan's problem, as you suggest, it is rlatively easy to construct a query that will give you the maximum value of Value A for each ID, but it will not point to the record in that table that has that value to get the values in the rest of the fields. Susan asked what are the values for ValueB and ValueC for the record that has that maximum value of ValueA for each ID.
Perhaps somehting like would in theory give the result:
select * from table Order by ID, ValueA Desc Group By ID Into OrderedList
Select ID, First(ValueA), First(ValueB), First(ValueC) from Orderlist Group By ID
But I have witnessed some strange results in SQL queries lately that I would not like to trust this approach too much. I certainly would not trust the results of this unless you saved the first query off as a table first.
r
On Thu, 13 May 2004 17:01:23 +1200, SCISOFT <[EMAIL PROTECTED]> wrote:
Susan / Robert
If you're dealing only with attributes, as I think, the first thing to do
would be to use some unique IDs rather than non-unique ones (add an extra
column).
But assuming you don't want to, it is very easy to construct an Access query
to do what you want -
SELECT Table1.nonUniqueID, Max(Table1.ValueA) AS MaxOfValueA FROM Table1 GROUP BY Table1.nonUniqueID;
(I just used Access's designer for this)
My result is - field headings nonUniqueID (ID), MaxOfValueA (ValueA)
ID ValueA ABC 50 XYZ 40
Constructing it in MapInfo SQL is another matter! Something for you to do..
Ian Thomas GeoSciSoft - Perth, Australian
-----Original Message-----
From: Susan Yu [mailto:[EMAIL PROTECTED]
Sent: Thursday, 13 May 2004 8:18 AM
To: [EMAIL PROTECTED]
Subject: MI-L [New Question] RE: MI-L Select Highest Value only in Table -
Multiple Records
Hello everyone,
I have a follow-up question since I'm doing something similar. I hope someone has a simple answer that I'm not seeing.
What if I wanted to select the RECORD with the highest value from a certain column?
I have multiple columns where they would all have a value with the same IDs. But not all the columns are consistantly the highest value.
For example:
ID ValueA ValueB ValueC XYZ 20 30 40 XYZ 30 15 20 XYZ 40 11 10 ABC 50 20 17 ABC 22 30 10
I would like to select only the records where ValueA is the higest (regardless of the values in ValueB and ValueC) I would like this result:
ID ValueA ValueB ValueC XYZ 40 11 10 ABC 50 20 17
I'm worried that if I used "group by ID", the query will return the ID, with the highest ValueA, but randomly pick the values for ValueB and ValueC.
Is there a solution? I would be grateful for any help.
Thanks, Susan
------------------------------------------
From: Cowper, Brian [mailto:[EMAIL PROTECTED] Date: 05-10-2004 13:37
Thanks Steve, that worked and thanks also to everyone else who came up =
with solution, I'm very impressed with the quick responses. I'll be back =
again when I hit that next wall!
Brian
-----Original Message----- From: Wallace, Steve [mailto:[EMAIL PROTECTED] Sent: Monday, May 10, 2004 4:19 PM To: Cowper, Brian Subject: RE: MI-L Select Highest Value only in Table - Multiple Records
Select WellID, Easting, Northing, Max(DepthFrom), Max(DepthTo)From YourTableNameGroup by WellID, Easting, Northing
Try that. -- Steve=20
-----Original Message----- From: Cowper, Brian [mailto:[EMAIL PROTECTED] Sent: Monday, May 10, 2004 04:16 PM To: MapInfo-L (E-mail) Subject: RE: MI-L Select Highest Value only in Table - Multiple Records
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
--------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | = To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11672
--------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11673
--------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11677
----------------------------------------- This email was sent using http://cafemail.dcccafe.com .
--------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11724
--------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11728
--
Robert Crossley Agtrix P/L 9 Short St PO Box 63 New Brighton 2483 Far Southern Queensland AUSTRALIA
153.549004 E 28.517344 S
P: 02 6680 1309 F: New Connection M: 0419 718 642 E: [EMAIL PROTECTED] W: www.agtrix.com W: www.wotzhere.com
--------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11729
