MySQL DISTINCT का उपयोग करके निश्चित संख्या में आने वाले फ़ील्ड्स लौटाना

निर्दिष्ट संख्या में आने वाले फ़ील्ड्स को लौटाने के लिए सिंटैक्स निम्नलिखित है:

select distinct yourColumnName, count(yourColumnName) from yourTableName where yourColumnName LIKE 'J%' group by yourColumnName having count(*) > 1 order by yourColumnName;

टेबल बनाना और डेटा डालना

mysql> create table DemoTable1500 (
    -> Name varchar(20)
    -> );
Query OK, 0 rows affected (0.86 sec)

mysql> insert into DemoTable1500 values('Adam');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1500 values('John');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1500 values('Mike');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1500 values('John');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1500 values('Jace');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1500 values('Jace');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1500 values('Jackie');
Query OK, 1 row affected (0.06 sec)

सभी रिकॉर्ड्स देखना

mysql> select * from DemoTable1500;

+--------+
| Name   |
+--------+
| Adam   |
| John   |
| Mike   |
| John   |
| Jace   |
| Jace   |
| Jackie |
+--------+
7 rows in set (0.00 sec)

J से शुरू होने वाले नाम जो एक से अधिक बार आते हैं

mysql> select distinct Name, count(Name)
    -> from DemoTable1500
    -> where Name LIKE 'J%'
    -> group by Name
    -> having count(*) > 1
    -> order by Name;

+------+-------------+
| Name | count(Name) |
+------+-------------+
| Jace |             2 |
| John |             2 |
+------+-------------+
2 rows in set (0.00 sec)