If NonKeyValue is the same for all rows in the group, you can add NonKeyValue to a GROUP BY clause or use DISTINCT instead od GROUP BY.
SELECT KeyValue,NonKeyValue FROM MyTable GROUP BY 1,2
SELECT DISTINCT KeyValue,NonKeyValue FROM MyTable
>I imagine this question has been discussed before. If so, could you point me to earlier threads or papers?
>
> In VFP8, for queries with a GROUP BY clause,we can no longer select columns that are not in the GROUP BY clause. This is reasonable since we can't guarrantee that those non-GROUP BY columns are the same in every row that contains the same GROUP BY column values.
>
> However, let's assume that the developer does know that the non-GROUP BY columns are identical and only wants one row per distinct GROUP BY value. For example, a cursor contains a key value and some other value which is known to be the same for idential key values. Is there any way to extract a cursor like we used to do:
>
> SELECT KeyValue,NonKeyValue FROM MyTable GROUP BY 1
>
> I can figure out how to do it using xbase commands using logic like this:
>
> 1. get list of unique key values
> 2. create empty cursor MyResultCursor consisting of 2 columns: key value, non key value
> 3. scan the list of unique key values
> for each key value, get the non key value from the original datar and create a row in MyResult Cursor
>
> Somehow it seems there must be a way using just SQL to do this, but I can't figure it out. Any ideas? Thanks
--sb--