CODEWITHSUNDEEP
sqldatabasems-accessms-access-2010
aboka
aboka

Reputation: 15

MS Access SQL Query Distinct Return Multiple Field

What I'd like to accomplish is querying a distinct field, field1, but return 2 fields' data. I'm using MS Access 2010 and field1 is Text and 'field2' is Date.

Here is the current code:

select distinct (field1) from tblOne where year(ddate) = 2016

I tried searching online about Grouping but when I try to query it says the field is 'not aggregate' (most probably as it's a Date type?)

The distinct field should be 'field1'.

Sample DB:

field1     field2
------     --------
0000       1/1/2016
0000       1/1/2016
0000       1/1/2017
0001       1/1/2016
0002       1/1/2015

and the query should return this:

field1     maxfield2
------     --------
0000       1/1/2017 (the latest date)
0001       1/1/2016
0002       1/1/2015

Upvotes: 0

Views: 363

Answers (1)

Daniel
Daniel

Reputation: 2804

You don't need distinct, distinct applies to all fields in the select not just the one immediately to the right of the distinct keyword. Distinct will get you a list of "distinct" rows, i.e, it just removes the duplicates.

You need a GROUP BY. This will show only one of each distinct combination of the grouped by fields, but then allows you to perform aggregates on other fields that are not grouped by. The aggregate you want here is MAX, as you said you wanted the latest—or greatest—date.

SELECT field1, MAX(field2) AS maxfield2
FROM tblOne
GROUP BY field1;

Upvotes: 2

Related Questions