MySQL GROUP BY

Perhatikan Query berikut :

mysql> select id,nomor, kode   from jawaban group by nomor, kode  having count(*) > 1 ;
+--------+------------+------+
| id     | nomor      | kode |
+--------+------------+------+
| 123673 | 2020800548 | 333  |
|  82207 | 2022800062 | 435  |
|  89616 | 2024200252 | 332  |
| 139145 | 2024801290 | 334  |
|  69294 | 2063702348 | 013  |
|  69100 | 2063703029 | 011  |
|  70004 | 2083700083 |      |
+--------+------------+------+
7 rows in set (0.17 sec)

Lalu :

mysql> select MIN(id),nomor, kode   from jawaban group by nomor, kode  having count(*) > 1 ;
+---------+------------+------+
| MIN(id) | nomor      | kode |
+---------+------------+------+
|  123673 | 2020800548 | 333  |
|   82207 | 2022800062 | 435  |
|   89616 | 2024200252 | 332  |
|  139145 | 2024801290 | 334  |
|   69294 | 2063702348 | 013  |
|   69100 | 2063703029 | 011  |
|   70004 | 2083700083 |      |
+---------+------------+------+
7 rows in set (0.17 sec)

Kemudian :

mysql> select MAX(id),nomor, kode   from jawaban group by nomor, kode  having count(*) > 1 ;
+---------+------------+------+
| MAX(id) | nomor      | kode |
+---------+------------+------+
|  124862 | 2020800548 | 333  |
|   82346 | 2022800062 | 435  |
|   90074 | 2024200252 | 332  |
|  140915 | 2024801290 | 334  |
|   77558 | 2063702348 | 013  |
|   77380 | 2063703029 | 011  |
|   78377 | 2083700083 |      |
+---------+------------+------+
7 rows in set (0.19 sec)

Kita bisa menentukan, dalam member group yg duplikat, data mana yang ingin kita ambil secara default.

Memilih Nilai Ke-n Pada Data Yang Dipisahkan Oleh Pembatas

Status : Draft

Dikutip dari [1] :

In general, to select the N-th value from a string VALUES that contains values separated by delimiter DELIM, you have to use: 

SUBSTRING_INDEX( SUBSTRING_INDEX( VALUES, DELIM, N ), DELIM, -1 )

Referensi

1. MySQL: select the n-th element from a delimiter separated value set, http://www.giannistsakiris.com/2015/01/23/mysql-select-the-n-th-element-from-a-delimiter-separated-value-set/

Membuang Semua Karakter Numerik dari Data

Alhamdulillah, sudah ada yang memberikan solusinya di [1] :

 DELIMITER $$

 DROP FUNCTION IF EXISTS `numericOnly`$$

 CREATE FUNCTION `numericOnly`(as_val VARCHAR(65535))
 RETURNS VARCHAR(65535)
 DETERMINISTIC
 BEGIN
   DECLARE retval VARCHAR(65535);
   DECLARE i INT;
   DECLARE strlen INT;
   -- shortcut exit for special cases
   IF as_val IS NULL OR as_val = '' THEN
     RETURN as_val;
   END IF;
   -- initialize for loop
   SET retval = '';
   SET i = 1;
   SET strlen = CHAR_LENGTH(as_val);
 do_loop:
   LOOP
     IF i > strlen THEN
       LEAVE do_loop;
     END IF;
     IF SUBSTR(as_val,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
       SET retval = CONCAT(retval,SUBSTR(as_val,i,1));
     END IF;
     SET i = i + 1;
   END LOOP do_loop;
   RETURN retval;
 END$$

 DELIMITER ;

Referensi

1. Remove all non-numeric characters from a field, https://stackoverflow.com/questions/31146942/remove-all-non-numeric-characters-from-a-field/31147842