SQL DISTINCT Command

SELECT DISTINCT class FROM student
Distinct SQL DISTINCT command in SQL collects the unique or distinct records from a field of a table. In the student table we are interested to know how many class records are there and the DISTINCT sql command should return class once only.

Unique is a reserved keyword used while creating table to add constraint to not to accept duplicate data.

So if class five is there ten times then it should return once and if class six one record is there then class six should return once.

Find duplicate records by using GROUP BY Query

How to delete duplicate records

Collecting unique data from columns of Database table by using DISTINCT query – SQL basics


There is another related command sql group by which groups the data and brings the unique names. This group by command is usually used along with count, average, minimum, maximum commands. Here we will discuss sql distinct command only
DISTINCT command will return records once only.

This is our table and we will apply DISTINCT command to this table.
idnameclassmark
1John DeoFour75
2Max RuinThree85
3ArnoldThree55
4Krish StarFour60
5John MikeFour60
6Alex JohnFour55
Here again the DISTINCT command in SQL
SELECT DISTINCT class FROM student 
The output is displayed here
class
Four
Three
As you can see only two rows are returned and they are the distinct class in the table

Counting number of distinct records

SELECT COUNT( DISTINCT class ) FROM student
Output is 7

MySQL Count Query

Null value with DISTINCT

NULL is considered distinct from all other non-NULL values. So, when we use DISTINCT query, the result set will include one row for each unique non-NULL value and one row for NULL (if NULL values are present in the column).
DISTINCT query with Null data

DISTINCT query using more than one column of a table

Now the distinct query can be applied using two columns. We know each class have two types of student ( male and female ). So each class will be returned twice, once with gender equal to male and other one will be when gender equal to female. Here is the query.
SELECT distinct class, gender FROM `student`
To make the list easily readable we will add order by query to this so we can list each class one after other.
SELECT distinct class, gender FROM `student` order by class
Here is a sample output, you will get more detail output when you use sql dump of student table given at the end of this page.
classgender
Fourfemale
Fourmale
Threefemale
Threemale
Down load the SQL DUMP of this student table

Distinct records linking two tables.

We will create two tables. One is our customer details and other one is with sales details. Same customer may buy more than once so there will be multiple entry for customer in our sales table.

We will try to get the name of the customer who has purchased any product. We will get distinct customer id from sales table and then link that to get the customer name from customer table. Here are our two tables.
customer
c_idname
1alex B
2Rohn C
3Ravi J
4Jack E
5Raju K
6Roy K
sale
sale_idc_idproductprice
12book40
22CD30
31Book50
43Pen20
54Bag30
63Cap15
Here is our query
SELECT DISTINCT sale.c_id, name FROM  `sale` , customer WHERE sale.c_id = customer.c_id 
The out put is here .
c_idname
2Rohn c
1alex B
3Ravi J
4Jack E

Unique data from two columns

Find out the list of teams participated in the tournament using the two columns Team_1 and Team_2.
Team_1Team_2Winner
IndiaSLIndia
SLAusAus
SAEngEng
EngNZNZ
AusIndiaIndia
SELECT Team_1 FROM icc_world_cup
UNION
SELECT Team_2 FROM icc_world_cup
Output
Team_1	
India
SL
SA
Eng
Aus
NZ
Read more UNION query

SQL Dump of icc_world_cup table
CREATE TABLE `icc_world_cup` (
  `Team_1` varchar(20) DEFAULT NULL,
  `Team_2` varchar(20) DEFAULT NULL,
  `Winner` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `icc_world_cup`
--

INSERT INTO `icc_world_cup` (`Team_1`, `Team_2`, `Winner`) VALUES
('India', 'SL', 'India'),
('SL', 'Aus', 'Aus'),
('SA', 'Eng', 'Eng'),
('Eng', 'NZ', 'NZ'),
('Aus', 'India', 'India');

Present in one and not present in other table.

How to identify the customers who have not purchased or who's id is not appearing in sales table?

For this we have to use left Join query

PHP Script to use Distinct SQL

Sample code using distinct query by using PHP script and PDO.
<?Php
require "config.php";// Database connection

$count="SELECT DISTINCT class, gender FROM student ORDER BY class";

echo "<table>";
echo "<tr><th>class</th><th>gender</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[class]</td><td>$row[gender]</td></tr>";
}
echo "</table>";
?>

Python script

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("mysql+mysqldb://root:pw@localhost/db")
try:
    r_set=my_conn.execute('SELECT DISTINCT class FROM student') 
    for row in r_set:
        print(row[0])
except OSError as e:
    print(e) # Specific error message 
    print ("Failed to delete %s " % path)
else:
    print ("Successfully Executed ")
MySQL & Python
How to delete Records in different tables
SQL References How to delete Records in different tables
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    kashif

    05-01-2009

    plus2net is gearttttt.....it is 1 of the best sites of da internet..i love this site for my problem solving n learning...plus2net is doing great job...best of luck....
    Shaveen Kaushal

    18-03-2009

    Thank u very much
    Kamran

    13-07-2009

    i want to learn sql, i am the new joiner, so please if have you any basic
    Alok Patoria

    21-01-2010

    i found dis helpfull.bt dere should be some heierarchy for the topics.
    plooger

    01-04-2010

    How could this be modified to list only the classes in which a given student is NOT enrolled?
    Rayudu

    05-04-2010

    How could this be modified to list only the classes in which a given student is NOT enrolled?
    xyzzx

    20-04-2010

    it is only a introductory knowledge.pls if posible provide a detailed description
    srinivas

    04-10-2010

    Pls. help me in getting % of records from a table.. ex: 1000 records in a table i want 20% of records..i.e 200 records....how we can do in single statement
    Bibin

    18-11-2010

    select top 20 percent * from Table_Name Order by Table_Column_Name
    kishore

    04-01-2011

    how to display each class(field) with number of student name
    jacob

    10-05-2011

    Hi, This site is great. I appreciate your effort over making this valuable website Thank you
    praveen

    21-06-2011

    sir.,with the unique or distinct value how to take the related field values.... i.e, "select * from tablename where DISTINCT phoneno=+TextBox71.Text+";
    jeheyr

    22-11-2011

    You should try "select DISTINCT phoneno, etc, etc, from tablename where phoneno = +TextBox71.Text+;" :)
    The Dod

    19-02-2012

    Thanks. Needed to sort timezones by gmtoff for something. SQL made my day.
    Roshan Pradhan

    23-02-2012

    plus2net is great site for programmer. thanks!
    swami naidu

    02-05-2012

    i am very happy to watch this site. it is very helpfull to me.. thanks alot...
    Rizwan

    04-08-2012

    sir, i want to return all fields using distint when i use "select distinct phone from talbe" it only retuns phone field but i also required sr in the same query look like this "select * from table distinct phone"
    Sahil Verma

    23-01-2013

    Thanx a lot...
    Anitha

    14-02-2013

    how to get top 10 distinct records modified recently
    umesh moradiya

    23-07-2013

    useful thank you....
    marcoaugustus

    25-07-2013

    I need more discussions about using distinct query between two tables with more fields
    Nitesh Srivastva

    16-06-2014

    hello sir,
    i have a table name emply in there some column like department,sex,id,qualification
    i want each department how much male and how much female on there in a query plz justify me...currently i am using postgresql.........
    smo

    16-06-2014

    Check group by
    ayushi

    18-06-2014

    in my table recreg by using command select distinct r_id from recreg .it returns not distinct value but why
    Sasikanth

    23-09-2014

    Very Useful Site.
    gagz

    25-08-2015

    i learned a lot from all the tutorial.Thank you so much plus2net.
    palaniappan praveen

    22-10-2016

    when i insert duplicate values one of the field name record is empty in phpMYADMIN
    Why? and how to solve this?
    I need the SQL syntax for only insert statements which we can insert duplicate values in the table..how to do that? I hope you understand my questions clearly!!!
    smo1234

    25-10-2016

    You can insert duplicate values if you don't have unique constraint for that field in your MySQL table. Remove that condition and see.
    priya

    17-08-2017

    How to write a sql query for , first day pen rate. 5 rs and 2nd day pen rate is 8 rs then what ill be the 3rd day pen rate. Write a query

    13-08-2019

    I stumbled on this while trying to resolve an issue and it was extremely helpful. Thanks and keep up the good work.

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer