>>>>>I need a SQL statement that will select a list of UNIQUE endors from a table and order them by last pay date. I am pulling the VENDID, VENDNAME, INVNUM, CHKNUM,CHKDATE,DTOS(CHKDATE), and CHKAMOUNT from a single table (CD). A simple select distrinct will not work because each vendor has multple records for different INVNUM's. Help Please
>>>>
>>>>Select * From vendors Into cursor tmp order by vendid,chkdate
>>>>Select * From tmp Group by vendid
>>>>BTW, your table structure implies that you better divide your table into two:
>>>>1. Vendors: vendid,name
>>>>2. Invoices: vendid,invnum,date
>>>
>>>Sorry forgot the Most important thing.
>>>This has to be a single SQL statement. This statement is used to feed a report in an accounting package written in Foxpro. We do not have access to source code and there is a hook for a sinle statement. Calling of outside programs is not directly supported.
>>
>>Select vendid,max(chkdata) as lastdata From vendors Group by vendid
>
>
>Thank you. That's got it. I knew there had to be a way of doing it.
>
but remember, that this one SQL-statement will provide proper results only for those fields which are the same for all group, i.e. don't try to retrieve Invnum using this command.