How to pick distinct row value based on column in sql/plsql

Question

Consider this table:

name  mark1 mark2 mark3
x      1      2    2
y      2      2    2
z      1      2    3

Here, I need to select the non distinct for example consider row three, where it contains only one "2" in column2. For this how to write a SQL code? I have made it of using the count and distinct commands but not able to get it.

count(distinct) is valid expression, do refer http://stackoverflow.com/questions/5737628/mysql-count-distinct — Tom Thomas, Dec 06 '13 at 05:28

score 1 · Answer 1 · answered Dec 06 '13 at 05:30

1

Try it this way

SELECT * 
  FROM table1 
 WHERE mark1 <> mark2 
   AND mark1 <> mark3 
   AND mark2 <> mark3;

Output:

| NAME | MARK1 | MARK2 | MARK3 |
|------|-------|-------|-------|
|    z |     1 |     2 |     3 |

Here is SQLFiddle demo

answered Dec 06 '13 at 05:30

peterm

88,818
14
143
153

Did it help? Do you need more help with your question? – peterm Dec 07 '13 at 10:09

score 0 · Answer 2 · edited Dec 06 '13 at 09:22

0

in case you need to select all columns contain values that are unequal

like in this case row3

SELECT * 
FROM table a 
WHERE a.m1<>a.m2 
  AND a.m2<>a.m3 
  AND a.m1<>a.m3

`see fiddle here`

edited Dec 06 '13 at 09:22

Hawk

4,912
11
44
71

answered Dec 06 '13 at 05:30

vhadalgi

6,871
6
36
67

score 0 · Answer 3 · answered Dec 06 '13 at 05:31

0

If I understand your question enough, it would be like this:

select * from mark where (mark1<>mark2 and mark1<>mark3 and mark2<>mark3);

answered Dec 06 '13 at 05:31

HENG Vongkol

767
7
9

How to pick distinct row value based on column in sql/plsql

3 Answers3

see fiddle here

`see fiddle here`