SQL DISTINCT Command

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. 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.

So using DISTINCT sql command we can avoid duplicate records in SELECT query

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.

SELECT DISTINCT class FROM student

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

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 sex equal to male and other one will be when sex equal to female. Here is the query.

SELECT distinct class, sex 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, sex 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.
classsex
Fourfemale
Fourmale
Threefemale
Threemale

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
Down load the SQL DUMP of this student table

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

We can use one sample PHP scrip to use distinct query.

<?Php
require "config.php";// Database connection

$count="SELECT distinct class, sex FROM student order by class";

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

Number of User Comments : 26


Google+

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.

Post Comment This is for short comments only. Use the forum for more discussions.






HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer