Search code examples
sqlvb.netms-accessoledb

Receive full row in distinct query

I have a table with let's say 3 Fields in a mdb-database:

ID | API-Key | Name

This table can contain multiple datasets where the API-Key can be identical.

Now I need to get only datasets with distinct API-Keys.

So For example with the following datasets:

1 | abc | Test1
2 | def | Test2
3 | abc | Test3
4 | xyz | Test4
5 | xyz | Test5
6 | abc | Test6

I need to get something like:

1 | abc | Test1
2 | def | Test2
4 | xyz | Test4

It doesn't matter which of the datasets with identical API-Key is returned.

I already searched google for about 30 min. but doesn't found anything matching my needs. Also tried around a bit but didn't found any working solution.

I hope someone has an idea.

Solution

  • Even if you don't care, the database engine needs to know which records/values to select. So you need some aggregate function, e.g. MIN()

    DISTINCT would only help if you just want to retrieve the API keys, if you want full records you need grouping.

    SELECT Min(ID) AS SomeID, API_Key, Min(Name) AS SomeName
FROM YourTable
GROUP BY API_Key