group results in sql

In my settings table there is a field whose content is:

  - Law 1  
  - Law 2  
  - Chemistry 1 
  - Chemistry 2 
  - History 1 
  - History 2
  - Modern language 1
  - Modern language 2
  - Modern language 3

(grades)

I wonder if there is an easy way to construct a SELECT sql query that returns:

• Law
• Chemistry
• History
• Modern language

The goal is to filter a portal that shows all teachers in order to show only teachers of a delimited subject.

Thanks in advance.

Answer

Filemaker supports only a limited subset of SQL. The following might work for you (assuming your numbers never go above 9):

SELECT DISTINCT LEFT(YourField, LENGTH(YourField) - 2) 
FROM YourTable

The goal is to filter a portal that shows all teachers in order to show only teachers of a delimited subject.

Not sure how the SQL query will help in this. The proper solution (as already suggested in the comments) is to have two separate fields, one for the subject and one for the ordinal number. Then the task becomes trivial.