Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Any idea what's wrong with the query

SELECT DISTINCT Table1.Description
FROM Table1
ORDER BY Table1.Description DESC;

I'm running this on MsAccess on a table where all fields are Memo. There are no indexes, primary or anything like that just a bunch of Memos. I'm getting the error Reserved error (-1060); there is no message for this error.

If you think my query is OK please let me know because I just had a problem with MDAC being corrupted and had to reinstall so it could be related to that.

Edit: OK I narrowed it down so you guys can try to repro:

  1. Create a blank database (table Table1 and ID field will be there by default) in MS Access.
  2. Add a field called Field1 of type Memo
  3. Add some random data and
  4. Try to run this query: SELECT DISTINCT Field1 FROM Table1 ORDER BY Field1

If you SELECT * it will work but won't be uniquer because it will consider the ID field.

If you don't get an error please let me know because that would mean my database engine is messed up.

share|improve this question
1  
Try and narrow it down: does select 1; work? select * from Table1;? –  Jack Douglas Sep 12 '11 at 8:39
    
@Jack: please see my edit. –  Ahmet Sep 12 '11 at 8:51
    
Have you seen this SO answer? It looks like distinct does not play nicely with memo. –  Jack Douglas Sep 12 '11 at 10:01

1 Answer 1

up vote 3 down vote accepted

Which MS Access version are you using?

Depending on MS Access version it's not possible to order by memo fields. You will need a workaround like:

ORDER BY Left(Description, 127) DESC
share|improve this answer
    
Should it work on 2010 version? –  Ahmet Sep 12 '11 at 9:41
    
ORDER BY should work with 2010, but it's not really sorting on the whole field content. But the problem in your case is the combination with DISTINCT, as Access is truncating internally the memo field to sort on it (like in my above sample). And that conflicts with DISTINCT. –  MicSim Sep 12 '11 at 10:13
    
Oh I think I got it now. When I use ORDER BY a memo, internally, the memo is not being selected anymore but a truncated version of the memo, and can't do DISTINCT on a field that is not listed after the SELECT statement. Actually for your sample to work I had to also list Left(Description, 127) after the SELECT as one more column. –  Ahmet Sep 13 '11 at 5:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.