Order by Clause conflicts with distinct in access?

Question

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

Ouch

WHat are you trying to do here? You should be able to achieve this by just ordering on grade, I would think. – Lizethlizette

I think you have a data storage issue if you need to sort by a different representation of the data than you use for display. The Val() function implies that you are converting a string value to a numeric value for sorting, which implies that you're storing something "98" and "100" as strings in the GRADE field. If those were numeric, you wouldn't need to convert them with Val(). If, on the other hand, the values actually are stored as numbers, then you're just wasting time and CPU Cycles passing them through the Val() function. – Nympha

Answer 1

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."

Pigeontoed

Answer 2

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]);

Biathlon

Answer 3

Using this:

SELECT DISTINCT Val([grade])
FROM tblStudents
ORDER BY Val([grade]);

Non-numeric records shows as 0.

But the below shows both numeric and non-numeric records:

SELECT grade
  FROM tblStudents
 GROUP BY grade
 ORDER BY VAL(grade), grade;

Heroin

Answer 4

A brute force and more generalized solution that doesn't consider the nuances of your coerced data type is to put the select distinct statement in a subquery, and the order by statement in an outer query:

select *
from
    (
         SELECT distinct grade
         FROM tblStudents
    )
order by Val([grade]),grade

Richierichlad