The standard way in SQL to find the data that corresponds to MIN/MAX is to use a correlated sub-query.
The example I have is:
select
jobid,
status,
supervisorid
from jobprogress m
where statusdate =
(select max(statusdate)
from jobprogress
where jobid = m.jobid)
The is the typical way to select 1 row for a given ID if there are multiple rows for each ID with a date for each row.
PF
>>>Imagine situation we want to find MIN value. But we can do nothing just with value, we need ID of record that contains MIN value. In VFP I can write
>>>SELECT MIN(MyNumber), ID_FIELD, category from Mytable group by category.
>>>
>>
>>Actually, this would contain ID of record that contains *one of the records* that satisfies the MIN() condition. There may be only one record that has the MIN(mynumber), but there may be more.
>>
>>You would need to group by category and id_field to get the true MIN(mynumber).
>>Since the lowest MIN(mynumber) of category+id_field will be the lowest MIN(mynumber) of category alone anyway, you use TOP to get the records with the lowest value of MIN(mynumber) from that group e.g.
>
>SELECT TOP 1 category, id_field, MIN(mynumber)
>> FROM mytable
>> GROUP BY category, id_field
>> ORDER BY 3
>
>>
>>If there is more than one record in the result set, then there is more that one category+id_field combination that has that MIN(mynumber).
>
> Actually this will not help, because what if we need to select all categories? There is solution, however, that is not efficient, using sub-query. (IMHO VFP query in such case MUCH more efficient).
(On an infant's shirt): Already smarter than Bush