Return the distinct highest values

[E3xtc]

Hi all,

Hopefully you can assist this pleb (me) with some (hopefully) basic scripting.

I have a table which has bucket loads of rows in it (funnily enough)
Field1 & Field2
Field1 is repeated numerous times (but also has differing values) through the table
Field2 is a numeric value which is assigned to the Field1

What I need to do is pull back all the unique Field1's but only those with the highest value in Field2

Example:
Field1 Field2
blah 100
blah 400
blah1 12
blah1 9
blah1 2
blah 350

I need the return to be basically:

Field1 Field2
blah 400
blah1 12

I am sure that this has to be straight forward - however my brain just isn't connected the right way at the moment....

Thanks in advance for any assistance on this.

Troy

 

[E3xtc]

doh - managed to get the advise from someone else

and yes it was painfully straight forward

Anyhow just for the future if anyone else runs into the same issue:

SELECT Field1, MAX(Field2) FROM Table GROUP BY Field1
**Thanks to RogerWilco on dbforums**

Cheers
Troy

 

[FredWagner]

Scanning past posts, before adding a new one - this one may help me - trying to select similar records with field one common to all, but other events with unique date fields, I want the most recent date record only for each common field. Will see if there's a function like MAX that works with date fields.

Fred Wagner
frwagne@longbeach.gov