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

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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...

Posted 26-Mar-15 3:37am

vermanishad

Updated 24-May-19 9:49am

OriginalGriff

v2

Add a Solution

Comments

ZurdoDev 26-Mar-15 9:38am

You can't do that. How would SQL know which id 1 row you want? The one with val1 of 33 or val1 of 32?

vermanishad 26-Mar-15 9:39am

i want only show distinct val2 arrange

ZurdoDev 26-Mar-15 9:41am

Well, same problem. How would sql know which row you want, the val2 that has id1 and val1 of 33 or the one that has id 1 and val1 of 32? You have to tell sql exactly what you want.

vermanishad 26-Mar-15 9:42am

id val1 val2 val3
1 33 m k
1 32 m k
2 34 j v
4 47 h l this is my value
only one distinct in used
the result should be
id val1 val2 val3
1 33 m k
2 34 j v
4 47 h l
val2 only mention distinct how to used plzz

ZurdoDev 26-Mar-15 9:46am

You are not understanding me.

You say you want the record
1 33 m k

But there is another record for m
1 32 m k

You have to tell sql which one of those you want. Can you explain which one you want in English?

Maciej Los 26-Mar-15 10:19am

MS Access or SQL Server?

vermanishad 27-Mar-15 2:27am

WITH cte AS
( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
FROM MyTable
)
SELECT *
FROM cte
WHERE rn = 1 this is a best result

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-03-26T04:12:00

Solution 1

Try:

SQL

WITH cte AS
(   SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
    FROM MyTable
)
SELECT *
FROM cte
WHERE rn = 1

Posted 26-Mar-15 4:12am

OriginalGriff

Comments

vermanishad 27-Mar-15 2:06am

thank you sir is working and thank for this solution very very much

pates 9-Dec-15 12:50pm

worked perfectly for me as well - thank you thank you!!

OriginalGriff 9-Dec-15 13:59pm

You're welcome!

SyedAhamed 7-Jun-18 3:14am

Perfect. Thank you

Maciej Los · Accepted Answer · 2015-03-26T04:24:00

Solution 2

Try this:

SQL

SELECT t1.<Field_List>
FROM TableName1 AS t1 INNER JOIN (
SELECT MAX(val1) AS val1
FROM TableName1 
) AS t2 ON t1.id = t2.id AND t1.val1 = t2.val1

Posted 26-Mar-15 4:24am

Maciej Los