Solved

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

Posted on 2014-02-05
5
32,192 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 42

Accepted Solution

by:
pcelba earned 500 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 33

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

789 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