Foxpro query to select a single record from each group

Question

I am hoping to find a simple SQL solution to my question. I have searched for one, but have been unable to find anything that works for my situation.

I have a .dbf with client data, consisting of customer names, addresses and other information, to be used for a commercial mailing. Each record gets assigned to a group, or package, and each package will be mailed separately. Occasionally I need to pull out records from one or more groups, into a new dbf, to set up as client seeds, updating them with new names and addresses, so the client will get a copy of the letter in the mail. Typically it's only one or two groups, in which case I can simply pick the first record, or find two sequential records that belong to different groups. Currently I have a file with 6 groups, and I need a single record from each group.

Basically I want to do the following, but I know this command doesn't work.

select * from customer group by package into table seeds

One way of doing this would be to do the following:

use customer
index on package unique
set order to package
copy to seeds
delete tag all

Alternatively, I could copy one record from each group into separate files, then append them to a single seed file.

Is there a way to get a single record from each group using a sql select statement?

Do you have any conditions to get the single record from each group like latest record something like that? — Viki888, Nov 09 '16 at 07:08
I'm not sure that I can, nor would it be useful. The client requires that we follow PCI (Payment Card Industry) rules, and for some of their mailings, including this one, the data they provide isn't the same. For example the non address data may consist of A, B, and C for this file, but next time it could be C, D, E, and F, or Q, R, and S. — Herb, Nov 09 '16 at 12:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/127721/discussion-between-viki888-and-herb-wolfe). — Viki888, Nov 09 '16 at 13:33

Cetin Basoz · Accepted Answer · 2016-11-09T08:19:56.757

3

Select * From Customers ;
    where CustomerId In ;
      (Select Min(CustomerId) From Customers Group By package) ;
    order By package ;
    into Cursor crsResult ;
    nofilter

This code assumes that you want to select one customer from each package group, regardless of which one that is (selecting the one with the min(customerId)) - ( it could be max(CustomerId) or, using longer SQL, pick top 1 from each group -like you did with unique- or, again with a much longer SQL, order in a particular order and pick Nth per group, or a random pick from each group if you need a randomization on selects).

Note: This is a hack that I wouldn't suggest really and relies on a bug in versions 7 and older. ie:

Set EngineBehavior 70
*** Last physically entered from each group
Select * From Customers ;
    group By package ;
    into Cursor result ;
    nofilter
Browse

*** Or ordered by something - this one corresponds to unique index trick
Select * from ;
 (Select * From Customers order by 1 desc) t ;
    group By Package ;
    into Cursor result ;
    nofilter
browse
Set EngineBehavior 90

edited Nov 09 '16 at 08:19

answered Nov 09 '16 at 08:09

Cetin Basoz

22,495
3
31
39

I had seen your suggested hack in my searches, and hoped to find an alternative, especially as I had no customer id field to allow me to use the first query. but was running short of time and used it anyway. However, I did some testing later, and got acceptable results using name or address data, and will use that in the future. – Herb Nov 10 '16 at 02:54
1

I see, you can create a 'virtual' customerId using recno() but a table should have a primary key anyway (in VFP's own samples we see one without PK :( ) – Cetin Basoz Nov 10 '16 at 11:04
These files aren't part of a production database, but are just single use files. Dbf just happens to be the standard format used in the commercial mailing industry here in the US. – Herb Nov 10 '16 at 12:16
1

Then with "select recno() as CustomerId, ... from Customers into cursor crsCustomers " you can create the virtual CustomerId - using recno() with joined tables is not reliable but you can use here on a single cursor/table. – Cetin Basoz Nov 10 '16 at 13:00

Foxpro query to select a single record from each group

1 Answers1