SELECT DISTINCT ORDER BY problem

[danb]

Hi, I have an Access table with two columns:

Column 1: Id (Primary key auto-number)
Column 2: RandomWords (Text field not sorted in any particular order)

All I want to do is select all DISTINCT random words without them being sorted alphabetically.

If I do:

SELECT DISTINCT RandomWords, Id FROM tblRandomWords ORDER BY Id;

It returns all rows - not just distinct random words.

If I do:

SELECT DISTINCT RandomWords FROM tblRandomWords;

It does select only distinct values but returns them alphabetically sorted. I want to keep the non-alphabetised sorting as in the original table.

Any help would be much appreciated, thanks!

 

[danb]

I've tried GROUP BY as well, and this has similar problems. You can't seem to be able to group by one field and order by another.

Is this correct?

 

[Mile-O]

Because you are selecting two fields - one being the words, one being an ID which I'm guessing is unique to that word.

Therefore:

---ID--- ---Word---
    01          art
    02          art
    03          bar
    04          bar
    05          cat

As you have two field it counts distinct across both fields so 01-art is distinct from 02-art, etc.

Ergo, you can't have the primary key in this query.

 

[danb]

Thanks, but wow! What a crazy limitation.

Okay, so if I remove the Primary Key field, then it still goups or selects distinct in alphabetical order. Is there a way just to pull distinct records from a table without them being alphabetised?

I can't believe something so basic can be so difficult!

 

[Rose412]

Try this query:-

SELECT RandomWords
FROM tblRandomWords
GROUP BY RandomWords
ORDER BY Min(ID);

 

[danb]

Thanks very much Rose, just the job! Much appreciated.

 

[rej2008]

Try this query:-

SELECT RandomWords
FROM tblRandomWords
GROUP BY RandomWords
ORDER BY Min(ID);

I know I am picking up on an old topic, but I am experiencing some issues where the above solution should've fixed the problem for me, but it is not. So I am hoping that you could perhaps help me further.

I am using Access 2003 (on work computer) on Windows XP. When I use the following command, instead of getting a unique list of items in the field, I am just getting empty spaces instead of items in my Combo box. If I remove GROUP BY, the items are listed, but ofcourse they are repeated as many times as they are in the table.

Here is the SQL that I am using:

SELECT tblStaff.JobTitle
FROM tblStaff
GROUP BY tblStaff.JobTitle;

The above combo box is on a form by itself with no other elements. I tried DISTINCT also, but the same problem... blank item list.

Any suggestions would be appreciated.