Solved

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

Posted on 2014-02-05
5
31,986 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 142

Expert Comment

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

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now