Click here to Skip to main content
15,217,528 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Updated 24-May-19 9:49am
v2
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

Try:
WITH cte AS
(   SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
    FROM MyTable
)
SELECT *
FROM cte
WHERE rn = 1
   
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
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try this:
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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100