The Directory will have two categories of members, paying and unpaying. When search listings are returned, they should be ordered as follows:
firstly paying members, longest standing members first (date joined is stored in the db)
then other members in alphabetical order.
I've thought of two possible ways of doing this
a) When search is run, run two queries (one for paying members, one for non-paying) to insert data into temporary table dynamically; temporary table contains an extra sort field calculated from date (for paying members) and from company name (for non paying members); then run standard dbman sql query to return data from temporary table, ordering by extra sort field
b) Similar idea, but add an extra hidden sort field to the company table. update this field whenever a record is added / changed, making sure paying members have a higher sort rank than non paying. sort query results by this field
Which of these, if any, sounds more feasible in dbman sql?
Many thanks for any help / suggestions.