CODEWITHSUNDEEP
sqlvbams-accessms-access-2007
DasPete
DasPete

Reputation: 851

access select distinct on certain column

I have a table with some search results. The search results maybe repeated because each result may be found using a different metric. I want to then query this table select only the distinct results using the ID column. So to summarize I have a table with an ID column but the IDs may be repeated and I want to select only one of each ID with MS Access SQL, how should I go about doing this?

Ok I have some more info after trying a couple of the suggestions. The Mins, and Maxes won't work because the column they are operating on cannot be shown. I get an error like You tried to execute a query that does not include the specified expression... I now have all my data sorted, here is what it looks like 

ID|Description|searchScore
97     test        1  
97     test        .95
120    ball        .94
97     test        .8
120    ball        .7

so the problem is that since the rows were put into the table using different search criteria I have duplicated rows with different scores. What I want to do is select only one of each ID sorted by the searchScore descending. Any ideas?

Upvotes: 2

Views: 6958

Answers (2)

HansUp
HansUp

Reputation: 97101

SELECT DISTINCT ID
FROM Search_Table;

Based on the last update to your question, the following query seems appropriate. 

SELECT ID, [Description], Max(searchScore)
FROM Search_Table
GROUP BY ID, [Description];

However that's nearly the same as Gordon's suggestion from yesterday, so I'm unsure whether this is what you want.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here is a way where you can get one of the search criteria:

select id, min(search_criteria)
from t
group by id

This will always return the first one alphabetically. You can also easily get the last one using max().

You could also use:

select id, first(search_criteria)
from t
group by id

Upvotes: 1

Related Questions