DISTINCT SQL Tutorial to get unique records from tables
SQL DISTINCT Command
SELECT DISTINCT class FROM student
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.
There is another related command
which groups the data and brings the unique names. This group by command is usually used along with , , ,
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
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
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.
Fourfemale
Threefemale
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.
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 .
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
PHP Script to use Distinct SQL
Sample code
distinct query by
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&&;
Comments : 28
▼ More on getting records from table with different combinations of commands
kashif05-01-2009plus2net 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 Kaushal18-03-2009Thank u very muchKamran13-07-2009i want to learn sql, i am the new joiner, so please if have you any basic Alok Patoria21-01-2010i found dis helpfull.bt dere should be some heierarchy for the topics.plooger01-04-2010How could this be modified to list only the classes in which a given student is NOT enrolled?Rayudu05-04-2010 How could this be modified to list only the classes in which a given student is NOT enrolled?xyzzx20-04-2010it is only a introductory knowledge.pls if posible provide a detailed description srinivas04-10-2010Pls. 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 statementBibin18-11-2010select top 20 percent * from Table_Name
Order by Table_Column_Namekishore04-01-2011how to display each class(field) with number of student namejacob10-05-2011Hi, This site is great. I appreciate your effort
over making this valuable website
Thank you praveen21-06-2011sir.,with the unique or distinct value how to take the related field values....
i.e, "select * from tablename where DISTINCT phoneno=+TextBox71.Text+";jeheyr22-11-2011You should try "select DISTINCT phoneno, etc, etc, from tablename where phoneno = +TextBox71.Text+;"
:)The Dod19-02-2012Thanks. Needed to sort timezones by gmtoff for something. SQL made my day.Roshan Pradhan23-02-2012plus2net is great site for programmer. thanks!swami naidu02-05-2012i am very happy to watch this site. it is very helpfull to me.. thanks alot...Rizwan04-08-2012sir,
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 Verma23-01-2013Thanx a lot...Anitha14-02-2013how to get top 10 distinct records modified recentlyumesh moradiya23-07-2013useful thank you....marcoaugustus25-07-2013I need more discussions about using distinct query between two tables with more fieldsNitesh Srivastva16-06-2014hello 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.........smo16-06-2014Check
ayushi18-06-2014in my table recreg by using command select distinct r_id from recreg .it returns not distinct value but whySasikanth23-09-2014Very Useful Site.gagz25-08-2015i learned a lot from all the tutorial.Thank you so much plus2net.palaniappan praveen22-10-2016when 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!!! subhendu25-10-2016You can insert duplicate values if you don't have unique constraint for that field in your MySQL table. Remove that condition and see.JavaScript Tutorial -
&&&& &Better Than Books - As Easy As It Gets!&