Q:

Order by Clause conflicts with distinct in access?

Please help me with this as I have been unable to get past this problem
When trying to execute this statement:
SELECT distinct grade
FROM tblStudents
ORDER BY Val([grade]),grade;

access tells me that ORDER BY clause Val([grade]) conflicts with Distinct
How can I fix this?
Thank you in advance

A:

You cannot order by a column thats not listed in a select distinct statement; if you want grade coerced to an integer;
SELECT DISTINCT Val([grade])
FROM tblStudents
ORDER BY Val([grade]);

A:

Using the DISTINCT keyword has the same effect as grouping by all columns in the SELECT clause: 
SELECT grade
  FROM tblStudents
 GROUP 
    BY grade
 ORDER 
    BY VAL(grade), grade;

Note I had to remove rows where grade IS NULL, otherwise I got an error, "Data type mismatch in criteria expression."

