Select 1st record for each 'code' in table with multiple records for each 'code'

[Rajesh Karunakaran]

I have a table with column 'code' and many other details. Table has multiple records for each 'code'.
What is the simplest/shortest method to select only the 1st record for each 'code'?

Thanks

 

[robsuttonjr]

SELECT code, MIN(recno()) AS minrecno ;
FROM yourTable ;
GROUP BY code ;
INTO CURSOR FirstRecords

SELECT yourTable.* ;
FROM yourTable ;
JOIN FirstRecords ;
ON yourTable.code = FirstRecords.code AND RECNO(yourTable) = FirstRecords.minrecno ;
INTO CURSOR FinalResult

 

[Rajesh Karunakaran]

Rob, thanks.
To test, I copied my table to 'yourtable'.
It gives error SQL: Column 'yourtable' not found.

In fact, I also was trying in the same way.
Are you sure this code works at your end?

 

[Chris Miller]

If you mean chronological, or simpler said by physical record order in the DBF the simplest thing you can do is index on code with a unique index.

Doing (once in the lifetime of the DBF only):

USE codes.dbf exclusive
INDEX ON code Tag ucode UNIQUE

See INDEX ON in help: https://www.vfphelp.com/vfp9/html/242d1feb-d43e-4831-9e4b-d0bb0b5fe4ae.htm
specifically the detail about UNIQUE indexes (not to be confused with the unique constraint on primary/candidate indexes):

UNIQUE stores the matching index key only for the first record that matches the specified index expression.
...
Any other index keys for records that match the index expression are excluded from the index file.

So that index becomes a filter to not only sort by code but also only list the first record of each code. When you SET ORDER TO ucode, you'd only get the first record per code.

The problem with that is, you will not be able to change this to something else, similar, like the TOP N records, or the 2nd, 3rd record, etc. But if you only need that, I'd go for such an index.

If you need to go into TOP N instead of only TOP 1, Tamar Granor has an article on that: http://www.tomorrowssolutionsllc.com/Articles/Getting the Top N for each Group.pdf

 

[Rajesh Karunakaran]

Chriss,

This is someone else's table! We don't have a Unique key set and I can't create one too!
So I solved it like below, I was trying for a shorter method though (why I posted here).

SELECT *, RECNO() rno FROM yourtable INTO CURSOR allrecs
SELECT ref, MIN(rno) AS rno FROM allrecs GROUP BY ref INTO CURSOR firstrecs

SELECT allrecs.* ;
FROM allrecs ;
JOIN firstrecs ON allrecs.rno = firstrecs.rno ;
INTO CURSOR FinalResult

By the way, if we use RECNO() in queries in order to get record number at run time, there are problems it seems.
I am not sure about what all and why. I didn't do any research for scenarios.

Thank you all,

 

[Chris Miller]

if we use RECNO() in queries in order to get record number at run time, there are problems it seems.

In general, this is true, using RECNO() in a query on a single table should work, but as the last query - also in Robs example - is a join, that could be ambiguous.

Your problem with "Column 'yourtable' not found." seems to be, because you did SELECT yourtable FROM yourtable... instead of SELECT yourtable.* FROM yourtable..., that's a shorthand for getting all columns (*), but only from yourtable, no fields/columns from the other joined FirstRecords table.

By the way, if you are shy to create an index on a table that doesn't belong to you, you could create an IDX index file that is separate. It wouldn't stay in sync with the DBF, though, i.e. you would need to update it by recreating it before using it:

INDEX ON code UNIQUE TO yourfile.idx COMPACT

That can also be a TEMP file or anywhere else, it's not updating with data changes anyway, but it's always possible to create that without exclusive access.

 

[Rajesh Karunakaran]

Chriss,

The table is not that big and I do not want to create an index as well. Also, with my query it fetches records I need fast.
Anyway, thank you for your input and idea on using an IDX freely in such circumstances.