Question 292482 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL SELECT only values with one occurence and the uniquesness depending on multiple columns

I have a table with columns:

KEY1, KEY2, KEY3, KEY6, PLT_ID

There are rows where different KEY6 values have other (KEY1, KEY2, KEY3, PLT_ID) same, like:

KEY1 KEY2 KEY3 KEY6 PLT_ID
1 1 1 X 1
1 1 1 Y 1

I need to filter only rows where there is only one occurrence of a combination of KEY1, KEY2, KEY3, PLT_ID for KEY6:

1,1,1,A,1 (where there is no other 1,1,1,*,1 in the table)

So when dataset will be:

KEY1 KEY2 KEY3 KEY6 PLT_ID
1 1 1 A 1
1 1 1 B 1
1 1 0 C 1
1 1 0 D 0
1 1 1 E 0
1 1 1 F 0

I want my result have only:

KEY1 KEY2 KEY3 KEY6 PLT_ID
1 1 0 C 1
1 1 0 D 0

because of unique combination of (KEY1, KEY2, KEY3, PLT_ID).

Tried :

SELECT KEY1, KEY2, KEY3, KEY6, PLT_ID FROM RAWDATA WHERE KEY6 = 'Outbound' GROUP BY KEY1, KEY2, KEY3, KEY6, PLT_ID HAVING (COUNT(KEY1) = 1 AND COUNT(KEY2)  = 1 AND COUNT(KEY6)  = 1 AND COUNT(PLT_ID)  = 1);

but it does not work.

Tags:

3

Answers


  1. 0

    Only group by the combination of keys you want to be unique, then you can just check for COUNT(*) = 1 to find the rows that are the only instance of that combination.

    The resulting SQL can look like this:

    -- Definition
CREATE TABLE example (
  key1 int,
  key2 int,
  key3 int,
  key6 varchar(255),
  plt_id int
);

INSERT INTO example VALUES
(1,1,1,'A',1),
(1,1,1,'B',1),
(1,1,0,'C',1),
(1,1,0,'D',0),
(1,1,1,'E',0),
(1,1,1,'F',0);

-- Query
SELECT * FROM example
GROUP BY key1, key2, key3, plt_id
HAVING COUNT(*) = 1;

    Example: http://sqlfiddle.com/#!9/f4798a/4

    Login or Signup to reply.
  2. 0

    The subquery will find the values you are looking for, which are only occuring once.

    SELECT *
FROM mytable
WHERE (KeY1,Key2,Key3, PLT_ID) IN (SELECT KeY1,Key2,Key3, PLT_ID 
                                   FROM mytable
                                   GROUP BY KeY1,Key2,Key3, PLT_ID
                                   HAVING count(*)=1)

    see: DBFIDDLE

    Login or Signup to reply.
  3. 0

    You can Group bythe four columns and check that a unique number of the columns exist only once.

    The unuque sum can be achieved ,y summing up the coumns but giving every columns a unique prime number

    SELECT 
`KEY1`, `KEY2`, `KEY3`, MIN(`KEY6`) as KEY6, `PLT_ID`  
FROM tab
GROUP BY `KEY1`, `KEY2`, `KEY3`, `PLT_ID`
HAVING COUNT(`KEY1` + 3*   `KEY2` + 5 *  `KEY3`+ 7 * `PLT_ID`) = 1
    KEY1 KEY2 KEY3 KEY6 PLT_ID
    1 1 0 C 1
    1 1 0 D 0

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search