Select First Row 2

[foxup]

Hi All,

I have DBF called (USS.DBF) with 3 fields called (City, Prov, Pcode) (no index set, but the records are already in order by 'PCODE'). Here are 7 sample records:

WESTWOOD, NJ, 07675
OLD TAPPAN, NJ, 07675
RIVER VALE, NJ, 07675
RIVERVALE, NJ, 07675
WOODCLIFF LAKE, NJ, 07677
WESTWOOD, NJ, 07677
WOODCLIFF LK, NJ, 07677

I would need to SELECT the "first" record of each DIST PCODE. So, my result would be:
WESTWOOD, NJ, 07675
WOODCLIFF LAKE, NJ, 07677

Would would I need for a "SELECT" command? Please help.


Thanks,
FOXUP!

 

[JRB-Bldr]

As with many things needing to be done in VFP there are a number of ways to approach it.
Also note that the approach you select may vary likely depend on what you want to do with the results.

One way that I use often is to use the INDEX to find the first occurrence.

* --- Make copy of table so as to leave original table in its original form ---
SELECT *;
   FROM USS;
   INTO CURSOR Frst READWRITE

SELECT Frst
DELETE ALL
INDEX ON PCode UNIQUE TAG Uniq
RECALL ALL  && RECALL only those which match the Index Expression (those which are the First Occurrence)

* --- Now only the first occurrence of PCode will Not be Deleted ---
SELECT Frst
* --- < Do Whatever > ---

Note that if you were to use this approach you MIGHT need to pay attention to your SET DELETED ON/OFF setting

SET DELETED OFF && DELETED Records are VISIBLE
SET DELETED ON && DELETED Records are HIDDEN

Also note that others may offer their own approach which may work equally well or maybe vastly better.

Good Luck,
JRB-Bldr

 

[TamarGranor]

I think this will do it. If not, it should give you the basic idea.

SELECT * ;
   FROM USS ;
     JOIN (SELECT MIN(RecNo()) AS nRec, PCode ;
            FROM USS ;
            GROUP BY PCode) csrMin ;
       ON USS.PCode = csrMin.PCode

Tamar

 

[foxup]

Hi JRB-Bldr & Tamar,

Both work great. Thanks to both you. Star for y'all!


Thank you,
FOXUP !!