How to have query display lowest value's

[sstump]

I'm trying to build a query in design view that will display the lowest values of a specified field. When I say lowest I mean all but the top value.

For example:

Field 1 Field 2
123456 120
123456 150
123456 190
234567 130
234567 210

In this example I would only want to show the lowest values in field 2, per field 1. So it would look like this.

Field 1 Field 2
123456 120
123456 150
234567 130

Basically removing the line with the highest value in field 2. Can this be done in design view or would this need to be done with an SQL query. If it needs to be done with an SQL query can someone help me write this?

Thanks for any help you can provide.

 

[lespaul]

How about:

SELECT FIELD1, FIELD2 FROM tblName T1 WHERE FIELD2 < (SELECT MAX(FIELD2) FROM tblName T2 WHERE T1.FIELD1 = T2.FIELD1)

replace FIELD1, FIELD2 and tblName with the correct information and this should run for you!

HTH

Leslie

 

[PHV]

Something like this ?
SELECT A.field1, A.field2
FROM theTable A LEFT JOIN
(SELECT field1, Max(field2) As MaxField2
FROM theTable GROUP BY field1) B
ON (A.field1=B.field1) AND (A.field2=B.MaxField2)
WHERE B.MaxField2 Is Null
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244