SELECT DISTINCT on one column, with multiple columns returned, ms access query

I have a query which returns about 20 columns , but i need it to be distinct only by one column.

my data looks something like that

id val1 val2 val3
1    33   m    k
1    32   m    k
2    34   j       v
4    47   h      l

the result should be
id val1 val2 val3
1    33   m    k
2    34   j       v
4    47   h      l

I have
Select Distinct id, val1, val2, val3
FROM table1

Group by doe not work either....

thanks for the help
maximyshkaAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
SELECT id, MAX(val1) as val1,  MAX(val2) as val2, MAX(val3) as val3 FROM YourTable GROUP BY id
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
ste5anSenior DeveloperCommented:
This is basically not possible. Cause DISTINCT is a operator on a set.

You can mimic it by using GROUP BY as the others already wrote, but this still is a major flaw: Your requirement of DISTINCT is not complete as it does nothing tell about how to handle the other columns.
0
 
maximyshkaAuthor Commented:
@steSan, I understand that . That is why i asked, i am not sure how to handle it in ms access.

@Guy Hengel - link provides samples for all dbs , but access.

@pcelba - your group by actualy works. Thanks!
0
 
samjomooreCommented:
The DISTINCT applies to each row, not just to the field that follows.  So in this case you will get combos of the different values of id val1 val2 val3.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.