Solved

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

Posted on 2014-02-05
5
31,852 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]
Comment Utility
0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
Comment Utility
SELECT id, MAX(val1) as val1,  MAX(val2) as val2, MAX(val3) as val3 FROM YourTable GROUP BY id
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
Comment Utility
@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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 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

11 Experts available now in Live!

Get 1:1 Help Now