Sql And Or In Where Query Commands And Statements

SQL AND OR in WHERE query Commands and statements SQL AND OR statement can be used with WHERE clause to list a set of records with matching combination of a table. We can use AND OR SQL in query with NOT combinationalso to filter out some records. This is our table

id name class mark 1 John Deo Four 75 2 Max Ruin Three 85 3 Arnold Three 55 4 Krish Star Four 60 5 John Mike Four 60 6 Alex John Four 55 7 My John Rob Fifth 78

We will apply AND command to display records of class Four with more than 70 mark. SELECT * FROM student WHERE class='Four' AND mark >70 id name class mark 1 John Deo Four 75 WE can see the record returned is class= Four and mark is more than 70. Here both the conditions are satisfied. SELECT * FROM student WHERE class='Four' OR mark >70 id name class mark 1 John Deo Four 75 2 Max Ruin Three 85 4 Krish Star Four 60 5 John Mike Four 60 6 Alex John Four 55 7 My John Rob Fifth 78

Here the records returned are either belongs to class Four OR having mark more than 70. Any one of the two condition is to be satisfied.

SQL AVG Command AVG sql command is used to find out average value of a numeric field in a table. Now let us apply this AVG command to this table and find out the average mark obtain by all the students. The AVG command will calculate the average value of all the marks

idname class mark 1John Deo Four 75 2Max Ruin Three 85 3Arnold Three 55 4Krish Star Four 60 5John Mike Four 60 6Alex John Four 55

Same way we can get the minimum value of a range of records by using SQL MIN command. Also check up SQL MAX command to get highest value of data We will apply the AVG command here like this to the field mark SELECT avg( mark ) FROM `student`

avg(mark)

65.0000 The command will calculate average value of marks considering all the marks of the table. We can define some header like this also. SELECT AVG(mark) as avg_mark FROM `student`

avg_mark

65.0000 Here we can use the Group By command to find out the average mark obtained by each classes. Related Tutorial • SQL Group by • MySQL Max • MySQL Min • MySQL Sum • Copy data to new table • SQL Left Join

SELECT class, avg( mark ) as avg_mark FROM `student` GROUP BY class class avg_mark

Four 62.5000 Three 70.0000 Please see the SQL SUM command to know the details on uses of GROUP BY command and the precautions.

We can add condition to the sql command to get our desired result. We can add one Where clause to the query to consider records for which mark is more than some value ( say 55 ) SELECT avg( mark ) as avg_mark, class FROM student where mark > 55 GROUP BY class

avg_mark class

65.0000Four 85.0000Three

SQL ALTER Command Table structure can be changed by using alter command. With this command Field type or property can be changed or a new field can be added. This sql alter table command is used like create table command Please note that while changing the structure we must honor the existing constraints of the table. For example if you decide to change a field to UNIQUE so it will not accept any duplicate records then if some records with duplicate value are already there then system will not allow this and we will get error message. Same way we have to take care of other constraints Here is the existing table structure.

Field

Type

Attributes Null Default

id

int(2)

No

name

varchar(50)

No

class

varchar(10)

No

mark

int(3)

No

Extra auto_increment

0

We will apply our alter table command to this table. We will change field name mark to student_mark Here is the command

ALTER TABLE `student3` CHANGE `mark` `student_mark` INT( 3 ) DEFAULT '0' NOT NULL

With this alter table command the the field name mark will change to student_mark. This way we are changing a field name only. Same way field type, default value and other properties of the field can be changed. The new table structure is listed below.

Field

Type

Attributes Null Default

Extra auto_increment

id

int(2)

No

name

varchar(50)

No

class

varchar(10)

No

student_mark

int(3)

No

0

Adding / Altering a numeric field to auto increment field You can read purpose and how to create auto increment field here. We can convert one existing numeric field to an auto increment field in two steps. First making the filed unique and then altering the field to add auto increment property to it. We will start with making it unique Declaring an numeric filed to be unique

$q="ALTER TABLE `message_table` ADD UNIQUE (`msg_id`)";

Here in the message_table we already have one numeric filed msg_id and we have made it to UNIQUE field so no duplicate data is allowed, if any duplicate data is there then we will receive error message. Now let us change it to add auto increment property to it.

$q="ALTER TABLE `message_table` CHANGE `msg_id` `msg_id` INT( 4 ) NOT NULL AUTO_INCREMENT ";

SQL BETWEEN Command to fetch records from a range

Many times we may require to find out records between a range of value. We can specify one upper limit and one lower limit and the query should return all the records between these two values. The sql BETWEEN command will give us the required result in all these cases. We will apply here BETWEEN command to a numeric field and see how the records are returned from a Mysql table. Here is our table.

idname class mark 1John Deo Four 75 2Max Ruin Three 85 3Arnold Three 55 4Krish Star Four 60 5John Mike Four 60 6Alex John Four 55 On this table we will apply our BETWEEN command to get all the records within some upper and lower limits. Say our upper limit is 75 and lower limit is 60. So we will get all the records within these limits and please note that limit 60 and 75 both are inclusive. Here is our sql BETWEEN command. SELECT * FROM `student` WHERE mark BETWEEN 60 and 75

id

name

class mark

1John Deo Four 4Krish Star Four 5John Mike Four

75 60 60

You can see we have all the records between 60 and 75 ( both inclusive). Please note that we have to first start with lower limit and then upper limit. So the records between 60 and 75 will be displayed ( NOT BETWEEN 75 and 60 )

SQL COUNT Command We can count the number of records in a table with different combinations. Let us first count the total number of records in the table with this count command.

SELECT count( * ) as total_record FROM student

This will display total records under the name total_record in the table student. Now we can add some condition to this SQL to count the records with different conditions. Let us find out the number of students in class Four in out table. SELECT count( * ) as total_record FROM `student` WHERE class = 'four'

• • • • • •

Related Tutorial SQL Group by MySQL Max MySQL Average MySQL Sum Copy data to new table SQL Left Join

This will return the count of students in class four only. Here since we are displaying only one count for a fixed class so we can use this way. We may require to display total of each class in a table so here we have to use GROUP BY clause to display totals of all the tables

SQL COPY TABLE Command For backup and other requirements we often have to copy the data of a table or copy the total table structure with data. We can selectively copy the data of a MySQL table to a new table or copy the total data to a new table. We will learn here different techniques on how to do this. We can copy a table structure and records to another new table. The CREATE TABLE command will create a table with same structure of the old table and add all the records. To export data to an existing table you can use insert command.

CREATE TABLE student2 SELECT * FROM student This will create a new table class2 using the structure of the table class and will copy all the records from table class to our new table class2. This became more useful when we add conditions to this by using SQL WHERE command. This way selectively we can transfer records to a new table. This is our table. id

name

class

mark

1

John Deo

Four

75

2

Max Ruin

Three

85

3

Arnold

Three

55

4

Krish Star

Four

60

5

John Mike

Four

60

6

Alex John

Four

55

We will apply our sql command to this table to create a new table and we will copy records for which class = Four. So our new table will contain the records of class four only.

CREATE TABLE student2 SELECT * FROM student WHERE class='Four'

Related Tutorial • • • • •

Exporting data to table SQL Inner Join Number of Affected rows SQL Rename table PHP MySQL functions

With this command we will create a new table student2 of same structure of main table student and all the records of class = Four will be copied to the new table. The new table student2 will have these records id

name

class

mark

1

John Deo

Four

75

4

Krish Star

Four

60

5

John Mike

Four

60

6

Alex John

Four

55

This way we can use any conditional requirements by using where clause to create or copy different tables.

Create table if not exists Note that all the above quires will return error if the table is already exist, so to prevent this error message we can add the command IF NOT EXISTS to the query.

CREATE TABLE IF NOT EXISTS student5 SELECT * FROM student WHERE class='Four' Here the table will be created only if the table is not there before. What we will do if we want to delete the old table and create a new table ?

DROP TABLE IF EXISTS Some time we may not be sure if the table exists or not so we can drop the table if exist by adding one more query before creating the table. Here it is

DROP TABLE IF EXISTS `student5`; The advantage of the above command over using a simple drop table command is here no error message saying unknown table is generated even if the table is not there.

Creating tables by query We can create new table by using sql. We have seen how to execute a query and get the records or modify the records in MySQL. Same way we can run one sql command to create tables. This can be a part of a script where based on the logic of the script we can design a table and create it.

We will start with simple create query for creating a new table.

$q="CREATE TABLE `sample_tb` (`empno` VARCHAR( 6 ) NOT NULL) "; The above query will create a table sample_tb and add one column empno to it. But note that we have to execute the above code. After execution we can find out whether the query has successfully executed or not by using one if condition. If the query is not executed successfully then we will print the error message. If table create process is successful then we will display a success message. Here is the code

$q="CREATE TABLE `sample_tb` (`empno` VARCHAR( 6 ) NOT NULL) "; $q1=mysql_query($q); if($q1){echo "created table sample_tb....
";} else{echo mysql_error();} As you can see we have created the table, with one column to store the data. Now what happens if the table sample_tb already exists and we will try to create again? The system will generate an error message. So before trying to create the table we will delete the table and then create again. ( Note: do this if your requirement is there ). We also can't delete the table without checking the table is there or not. So we will use one if exists command like this.

$q="DROP table if exists sample_tb"; $q1=mysql_query($q); if($q1){echo "deleted the table sample_tb....
";} else{echo mysql_error();} We can easily execute the create table query to generate the table. Note that this combination is used inside many scripts where temporary tables are create and deleted at the end.

SQL delete Command Delete query is used to delete records from the table. This query is used along with some condition to selectively delete records from the table. To delete all the records from the table we can use truncate command like this

TRUNCATE `student` This will remove all the records from the table. This is one way of deleting all the records from a table. Now let us try to delete records based on some selection. Now let us delete records of the students who has got mark less than 60. Here we are selectively deleting the records so we have to use WHERE clause to match the selection.

Related Tutorial • • • • •

Number of Affected rows Copy data to new table SQL Left Join SQL Rename table PHP MySQL functions

DELETE FROM student WHERE mark < 60

This will delete records for which mark is less than 60. We can use all the WHERE clause we learned at SQL WHERE page. How to delete tabel or database ?

SQL DROP to remove table We have seen how to remove records from a table by using truncate sql command. Now we will learn how to use DROP query command to delete a table or a field or some other properties associated with the table. Let us start with deleting a field by DROP command.

ALTER TABLE `content` DROP `dt`; Here content is our table name and dt is one of the field of this table. By this command we can delete the field dt of the table content.

Deleting a Table We can use DROP command to remove a table inside the database. Now here is the code to delete the table content.

DROP table content Deleting multiple tables We can use drop command to delete more than one table. Here is the command to remove 4 tables.

DROP TABLE `content`, `content_admin`, `content_cat`, `content_cmt_post`; The above command will delete four tables.

Dropping a unique constraints We can use DROP sql command to remove unique constraints associated to any column, here is an example.

ALTER TABLE 'content_cat' DROP INDEX 'cat_id' The above command will remove the unique index associated with cat_id field of content_cat table We can also use DROP command to delete a complete database. Here is the sample code

DROP DATABASE TEST

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.

idname class mark 1John Deo Four 75 2Max Ruin Three 85 3Arnold Three 55 4Krish Star Four 60 5John Mike Four 60 6Alex John Four 55

• • • • •

Related Tutorial Group by SQL Maximum value SQL Average SQL Rename table PHP MySQL functions

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

SQL GROUP BY Command You can see the Count command before using GROUP BY command here. GROUP BY command will create groups in the field name specified and will count the number of records in the groups. This is very useful command. We can also use WHERE command along with GROUP BY command in Mysql tables. SELECT count(*) as total_records, class FROM `student` group by class This will display the total records in each class. Like this

total_records

class

1

Eight

3

Five

9

Four

2

Nine

10

Seven

7

Six

3

Three

Let us try to get the total number of girls records in each class by using GROUP BY query. Here we want to filter our query for only girls so we have to use one WHERE clause to restrict the records using the sex field. Here is the query.

• • • • •

Related Tutorial Copy data to new table SQL Left Join Number of Affected rows SQL Rename table PHP MySQL functions

SELECT count( * ) AS total_records, class FROM `student` WHERE sex='female' GROUP BY class

The output is here total_records

class

5

Four

1

Nine

5

Seven

5

Six

1

Three

SQL HAVING command used with GROUP BY Command Please read basic of group by command Part I We can use sql having command to add condition to the query. The advantage of using HAVING command is it can be used in aggregate functions like count, max etc. We can't use SQL WHERE clause here. This way we can add value to our select command as per requirement. Usually SQL HAVING command is used in conjunction with group by command. Please read the sql group by command and SQL sub group by command to know more about grouping the records. Here is the full table with all the rows. On this we will apply the group by query

d_id

name

type

category

price stock

1Book1 Book Management 2Book2 CD Management 3Book3 Report Management 4Book4 Book Management 5Document1 Book Management 6Document2 CD Computers 7Document3 Report Computers 8Book5 Report Management 9Document4 CD Management 10Book 8 Book Computers 11Book 9 CD Computers 12Document5 Report Computers 13Book 10 Book Computers

220 120 25 55 15 80 55 80 72 88 100 85 150

10 8 4 8 23 45 65 10 5 6 5 8 5

Here is the SQL having command with group by command applied on the above table. SELECT category, type , count( * ) as total FROM `documents` GROUP BY category, type HAVING total < 3 This will display the total records in each group and sub group. Like this

category

type total

Computers Book Computers CD Computers Report Management CD Management Report

2 2 2 2 2

• • • • •

Related Tutorial SQL Group by Copy data to new table SQL Left Join SQL count PHP MySQL functions

Without the HAVING command you will get a result records like this below. SELECT category,type, count(*) as total FROM `documents` group by category,type This will display the total records in each group and sub group. Like this

category

type total

Computers Book Computers CD Computers Report Management Book Management CD Management Report

2 2 2 3 2 2

SQL INSERT Command INSERT command is used to append or add a record to a database. A new record will be added at the end of the table every time INSERT command is used. We have to specify what are the fields to be filled while inserting the record, we can add records even without specifying the field names but we have to maintain the order of the filed existed in the table and the the order of the data we are inserting. We have to take care of proper formatting of the data we are inserting to the table. We can't insert a string to a numeric field. So we have to format the data and apply the insert command. Here is one example of insert

command. INSERT INTO table_name(field1,field2,field3,field4) values('value1','value2','value3','value4') Here the order of the fields need not be same as order in our MySQL table but the order we are specifying by saying the filed names, that order we must maintain for values. We have to take care of the fields where we have given NOT NULL or any other such requirements. Any violation of the field structure will generate an error message. Note that it is always a good practice to use filed name and value pairs while inserting records. If we specify only the value by maintaining the order of the fields there will be problem in future once a new field is added or removed from the table. So always write the query specifying field names and its values. Here are some sql commands that will create a table and then add three records to it by using insert command. CREATE TABLE t1 (id int(11) NOT NULL default '0',name1 varchar(10) NOT NULL default '') TYPE=MyISAM; Related Tutorial • SQL Insert using SET option • SQL Select Query • Copy data to new table • SQL Left Join

Adding records to the table t1 INSERT INTO t1 VALUES (1, 'one1'); INSERT INTO t1 VALUES (2, 'two1'); INSERT INTO t1 VALUES (3, 'three1');

SQL INNER join query for MySQL INNER join SQL command is mostly used to join one table to it self. The biggest advantage of doing this is to get linking information from the same table. We will try to understand this with an example. The best example of INNER join will be employee table where we will keep the employee and its manager as a single record. This way by linking to the table it self we will generate a report displaying it as two linked tables. Each record will have one additional field storing the data of the manager by keeping the employee ID and we will use M_ID ( manager ID ) to link with main employee ID. This way we will link two virtual tables generated from one main table. Here is the table. You can download /copy the sql dump file to create your own MySQL table for testing.

Main table id

name

Managers Table

Employee table

m _id

id emp_na me

id emp_na me

1

John

2

2

Greek Tor

1

John

2

Greek Tor

1

1

John

2

Greek Tor

3

Alex John

1

1

John

3

Alex John

4

Mike tour

1

1

John

4

Mike tour

5

Brain J

3

3

Alex John

5

Brain J

6

Ronald

3

3

Alex John

6

Ronald

7

Kin

4

4

Mike tour

7

Kin

8

Herod

3

3

Alex John

8

Herod

9

Alen

2

2

Greek Tor

9

Alen

10

Ronne

1

1

John

10

Ronne

In the table you can see every record has one manager id field known as m_id. We have used the unique id of the employee in the m_id field to mark who is the manager for the employee. To generate the manager table we have used this SQL SELECT t1.id,t1.name as emp_name from emp as t1 INNER JOIN emp as t2 on t1.id=t2.m_id To generate the employee table we have used this SQL SELECT t1.id,t1.name as emp_name from emp as t1 INNER JOIN emp as t2 on t2.id=t1.m_id Now let us use inner join to create one report on who is the manager of which employee. Check this SQL SELECT t1.id, t1.name as emp_name, t2.name as manager FROM emp as t1 INNER JOIN emp as t2 on t2.id = t1.m_id

id emp_na manager me 1

John

Greek Tor

2

Greek Tor

John

3

Alex John

John

4

Mike tour

John

5

Brain J

Alex John

6

Ronald

Alex John

7

Kin

Mike tour

8

Herod

Alex John

9

Alen

Greek Tor

10

Ronne

John

Related Tutorial • MySQL Left Join • SQL select • Number of Affected rows

CREATE TABLE `emp` ( `id` int(4) NOT NULL auto_increment, `name` varchar(25) NOT NULL default '', `m_id` int(4) NOT NULL default '0', UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; --- Dumping data for table `emp` --

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

`emp` `emp` `emp` `emp` `emp` `emp` `emp` `emp` `emp` `emp`

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1, 'John', 2); (2, 'Greek Tor', 1); (3, 'Alex John', 1); (4, 'Mike tour', 1); (5, 'Brain J', 3); (6, 'Ronald', 3); (7, 'Kin', 4); (8, 'Herod', 3); (9, 'Alen', 2); (10, 'Ronne', 1);

SQL IN query Command and statements SQL IN statement can be used with where clause to list a set of matching records of a table. We can use SQL in query with NOT combination also to filter out some records. This is our table

id

name

class mark

1John Deo Four 2Max Ruin Three 3Arnold Three 4Krish Star Four 5John Mike Four 6Alex John Four 7My John Rob Fifth

75 85 55 60 60 55 78

We will apply in command to display records of class Four and Fifth only.

SELECT * FROM `student` WHERE class IN ('Four','fifth')

id

name

class mark

1John Deo Four 4Krish Star Four 5John Mike Four 6Alex John Four 7My John Rob Fifth

75 60 60 55 78

Here above the sql in query returns the records of class Four and Fifth only. We can apply NOT IN sql query also like below to remove some records in display.

SELECT * FROM `student` WHERE class not IN ('Four','fifth')

id

name

class mark

2Max Ruin Three 3Arnold Three

85 55

We can link or collect data related to two different tables by using IN command. For example we have another sports table where we have the list of students joined in sports. We need not keep all the columns lime name , class etc. We can only keep the id filed. So our student can be collected from main table. Here is the query which will display only students joined in sports

Select * from student where id in ( select * from student_sports)

SQL left join query We can link more than one table to get the records in different combinations as per requirement. Keeping data of one area in one table and linking them each other with key field is better way of designing tables than creating single table with more number of fields. For example in a student database you can keep student contact details in one table and its performance report in another table. You can link these two tables by using on unique student identification number.

Here are two tables with some data. We will apply first sql where command to this table

Table one ( t1 )

Table two ( t2)

id name1 1one1 2two1 3three1

id name2 1one1 2two2 4four2

SELECT t1.id,t1.name1 FROM `t1`,t2 WHERE t1.id=t2.id The output of the above sql command will give records of MySQL table t1 for which there is a record present in table t2 and linking is done by id field.

i d

n ame 1

1one1 2two1 We can display all the records of table t1 along with the (matching ) records of t2 if matching is found by linking them in ID field. Here is the left join sql to display this and below that the output is listed. SELECT t1.id, name1, t2.id, t2.name2 FROM t1 left join t2 on t1.id = t2.id

i d

n ame 1

1one1 2two1

id

n ame 2

1one1 2two2

3three1 NULLNULL The above result shows a simple left join and its output. We may be interested to identify the the records in table t1 for which there is no record present in table t2. Here we have to use left join and link the tables by id field. Here is the query using left join and the out put is below that. SELECT t1.id, name1 FROM t1 left join t2 on t1.id = t2.id where ISNULL(t2.id) for MySQL 5 and above try like this SELECT t1.id, name1 FROM t1 left join t2 on t1.id = t2.id where (t2.id) is null

i d

n ame 1

3three1 In the above result we have seen how the output works when we have to get records which are not present in other table. Now let us find out the other way , we will get the record of table one for which matching record exists in table 2. Here is the left join sql and below that the results of the query. SELECT t1.id,name1,t2.id,t2.name2 FROM t1 left join t2 on t1.id = t2.id where NOT ISNULL( t2.id ) for MySQL 5 try like this SELECT t1.id,name1,t2.id,t2.name2 FROM t1 left join t2 on t1.id = t2.id where ( t2.id ) NOT is null

i d

n ame 1

1one1 2two1

i d

n ame 2

1one1 2two2

SQL Limit query for a range of records in MySQL table We may require to display some part of the records returned by a query specifying a range. What is the range to be returned we can specify by saying the starting and ending record number. We will pass this starting and ending number along with the SQL LIMIT command to restrict the records within that range. We will see the syntax of this query in our student MySQL table. SELECT * FROM `student` LIMIT 0, 10 We have specified here to return 10 records starting from 0 or from the first record. Same way we can ask for 10 records starting from 20th record like this

SELECT * FROM `student` LIMIT 20, 10 This will return 10 records from 21st record. That is from 21st record to 30th record. Here is the output

id

name

clas m s ark

2Babby Four 1John 2Reggid Seve 2 n 2Herod Eigh 3 t 2Tiddy Seve 4Now n 2Giff Tow Seve 5 n 2Crelea Seve 6 n 2Big Nose Thre 7 e 2Rojj Base Seve 8 n 2Tess Seve 9Played n 3Reppy Six 0Red

69 55

• • • • •

Related Tutorial PHP paging Copy data to new table SQL Left Join SQL Rename table PHP MySQL functions

79 78 88 79 81 86 55 79

This is quite useful for designing paging in any script. Paging is known as displaying records in page wise with fixed number of records per page. There will be navigational menu to move between any pages and go to next and previous pages. The best example of php paging is the way google display search results. It display an easy navigational menu at the bottom of each search result page to go to next or previous page or any other page. Note that this is returning number of records and this has no connection with the id number field used here. If the ID numbers are different then also the query will return 10 records starting from 21 record ( irrespective of the ID numbers ). If you want records with particular ID range specified then you have to use sql between command.

SQL LOCATE command to search strings in fields We can use the LOCATE string command in our SQL to get the position of a string present inside another string. Let us try this command.

select locate('xy',afghytyxyrt)

This query will return 8 as the searched string is located at the 8th position within the string. Now let us apply this LOCATE command to search for the presence of the name john in the name field (column ) of our student table.

SELECT * FROM `student` WHERE locate( 'john', name )

The output of this query is here.

name

class mark sex

John Deo Four5 75 John Mike Four5 60 Alex John Four5 55 My John Rob Fifth5 78 Big John Four5 55 Babby John Four5 69

male male male male male male

As you can see we have collected all the records having 'john' in any place in the name column of the student table. Now let us apply this Locate query to our student table to search for the location of the string 'john'

SELECT locate('john',name) as position,name FROM `student`

The above command will return all the records of with the position of the searched string, if it is not found then 0 is returned.

SQL MAX Command Some time we will be searching for the maximum value in a field of any MySql table. MAX sql command will return the record with maximum or highest value in the SQL table. Same way we can get the minimum value of a range of records by using SQL MIN command What happen if we apply MAX command to a non numeric field? We will get the record with highest alphabet. But the best way to use MAX command is to apply it in a numeric field.

id

name

class

mark

1

John Deo

Four

75

2

Max Ruin

Three

85

3

Arnold

Three

55

4

Krish Star

Four

60

5

John Mike

Four

60

6

Alex John

Four

55

We will apply the MAX command here like this to the field mark

SELECT max( mark ) FROM `student`

max(mark) 85

The command collected the maximum value of the mark field and displayed. We can define some header like this also.

SELECT MAX(mark) as max_mark FROM `student` max_mark 85

To display all fields like name, class, id along with the highest mark we can use like this. This will display one record with all details of the highest mark

SELECT id,name,class,MAX(mark) as max_mark FROM `student` id

name

2

class

Max Ruin

Three

max_mark 85

We can get same output by using where clause by matching with maximum mark

SELECT * FROM `student` WHERE mark=(select max(mark) from student) Using Group By Now let us find out what is the maximum mark ( or highest ) in each class. Here we can use the Group By command to find out the maximum mark obtained by each class

SELECT class, max( mark ) as max_mark FROM `student` GROUP BY class class

max_mark

Four

75

Three

85

You can see above that maximum mark of each class is displayed. Since we have two class in our table so the sql command has returned two class with highest mark in each of them. We have to use Group By clause if we ask for the query to return any other field name other than the max. Otherwise system will generate error.

Related Tutorial • MySQL Min • MySQL Avg • MySQL Sum

We can add condition to the sql command to get our desired result. We can add one Where clause to the query to consider records for a perticular class only ( say Four)

SELECT max( mark ) as maximu_mark, class FROM student where class ='Four' GROUP BY class max_mark 75

class Four