The way I found about about doing it this way is because of my current job. I'm now doing mostly Oracle work. In order to get the job I had to take a little quiz and the "bonus" question was about selecting 1 row for each ID based on data_date. I didn't get the question right, but I got the rest of them, which nobody else did.

After I got hired, I was doing some research on SQL Selects when I happened upon a whitepaper on doing correlated sub-queries.

PF

>I know - to make difference and refer field from parent query in child query. Nice approach, and I think it should be in FAQ for VFP - SQL Server solutions.
>
>>Yes. The important thing to note is that they alias the table in the main part of the select.
>>
>>PF
>>
>>>I guess category (i.e. grouping field) is jobid, right?
>>>
>>>
>>>>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