Find record where a field indicates the lowest value

[demusoli]

@ All, I do not know, how to get my query done. Here is the situation:

I have a table with the following data:

Table:
JobNo as String
Device as String
Price as double

Data:
JobNo Device Price
--------------------------------------
123 DeviceA $100
123 DeviceB $110
123 DeviceC $115
124 DeviceA $200
124 DeviceB $195
124 DeviceC $215

The result I would like to have is:
JobNo Device Price
--------------------------------------
123 DeviceA $100
124 DeviceB $195

Basically I am looking for a query, which returns the cheapest device,
to produce the job.

Thanks in advance,
Oliver

 

[John Spencer]

Two query solution

SELECT Device, Min(Price) as LowPrice
FROM YourTable
GROUP BY Price

SELECT T.JobNo, T.Device, T.Price
FROM YourTable as T INNER JOIN TheSavedQuery as LP
ON T.Device = LP.Device and
T.Price =LP.LowPrice

All in one query would be

SELECT T.JobNo, T.Device, T.Price
FROM YourTable as T INNER JOIN
( SELECT Device, Min(Price) as LowPrice
FROM YourTable
GROUP BY Price
) as LP
ON T.Device = LP.Device and
T.Price = LP.LowPrice

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Douglas J. Steele]

I believe that the subquery should be

SELECT Device, Min(Price) as LowPrice
FROM YourTable
GROUP BY Device

not

SELECT Device, Min(Price) as LowPrice
FROM YourTable
GROUP BY Price

 

[demusoli]

Thanks all,
this one works now, I wanted to have it by job number.

SELECT T.JobNo, T.Presses, T.Price
FROM tblData as T INNER JOIN
( SELECT JobNo, Min(Price) as LowPrice
FROM tblData
GROUP BY JobNo
) as LP
ON T.JobNo = LP.JobNo and
T.Price = LP.LowPrice

Thanks again,
Oliver

 

[John Spencer]

Ouch! Good catch. Thanks for the backup

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================