?
Solved

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

Posted on 2014-02-05
5
Medium Priority
?
33,166 Views
Last Modified: 2014-12-11
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
0
Comment
Question by:maximyshka
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39835855
0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 39835909
SELECT id, MAX(val1) as val1,  MAX(val2) as val2, MAX(val3) as val3 FROM YourTable GROUP BY id
0
 
LVL 36

Expert Comment

by:ste5an
ID: 39838261
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
 

Author Comment

by:maximyshka
ID: 39838931
@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
 
LVL 1

Expert Comment

by:samjomoore
ID: 40494738
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question