SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL

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 : 25


Google+

kashif05-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 Kaushal18-03-2009
Thank u very much
Kamran13-07-2009
i want to learn sql, i am the new joiner, so please if have you any basic
Alok Patoria21-01-2010
i found dis helpfull.bt dere should be some heierarchy for the topics.
plooger01-04-2010
How 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-2010
it is only a introductory knowledge.pls if posible provide a detailed description
srinivas04-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
Bibin18-11-2010
select top 20 percent * from Table_Name
Order by Table_Column_Name
kishore04-01-2011
how to display each class(field) with number of student name
jacob10-05-2011
Hi, This site is great. I appreciate your effort
over making this valuable website
Thank you
praveen21-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+";
jeheyr22-11-2011
You should try "select DISTINCT phoneno, etc, etc, from tablename where phoneno = +TextBox71.Text+;"
:)
The Dod19-02-2012
Thanks. Needed to sort timezones by gmtoff for something. SQL made my day.
Roshan Pradhan23-02-2012
plus2net is great site for programmer. thanks!
swami naidu02-05-2012
i am very happy to watch this site. it is very helpfull to me.. thanks alot...
Rizwan04-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 Verma23-01-2013
Thanx a lot...
Anitha14-02-2013
how to get top 10 distinct records modified recently
umesh moradiya23-07-2013
useful thank you....
marcoaugustus25-07-2013
I need more discussions about using distinct query between two tables with more fields
Nitesh Srivastva16-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.........
smo16-06-2014
Check group by
ayushi18-06-2014
in my table recreg by using command select distinct r_id from recreg .it returns not distinct value but why
Sasikanth23-09-2014
Very Useful Site.
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer