SQL BETWEEN Command to fetch records from a range

SQL BETWEEN Many times we may require to find out records between a range of values.
We can specify one lower limit and one upper limit for column and the query will return all the records between these two values.

SQL BETWEEN to get rows within a range with all other commands


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.
idnameclassmarksex
1John DeoFour75female
2Max RuinThree85male
3ArnoldThree5male
4Krish StarFour60female
5John MikeFour60female
6Alex JohnFour55male

 
On this table we will apply our BETWEEN command to get all the  records within some upper and lower limits. Say for our mark column upper limit is 75 and lower limit is 60. So we will get all the records within these limits and note that limit 60 and 75 both are inclusive. Here is our sql BETWEEN command.

SELECT * FROM `student`WHERE mark  BETWEEN 60 and 75
idnameclassmarksex
1John DeoFour75female
4Krish StarFour60female
5John MikeFour60female
18HonnyFive75male
20JacklyNine65female
21Babby JohnFour69female
34Gain ToeSeven69male

You can see we have all the records between 60 and 75 ( both inclusive).
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 )

How many records are there within this limit ( USE count() )?
SELECT count(*) FROM student WHERE mark BETWEEN 60 and 75;
7

Using NOT

SELECT * FROM `student`WHERE mark NOT  BETWEEN 50 and 100
Output
idnameclassmarksex
19TinnyNine18male

Displaying from highest to lowest mark

Using order by we can change the above display and show from highest to lowest.
SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 order by mark desc
By using DESC we are getting results from highest to lowest. By default it is from lowest range to highest range. Output is here
idnameclassmarksex
1John DeoFour75female
18HonnyFive75male
21Babby JohnFour69female
34Gain ToeSeven69male
20JacklyNine65female
4Krish StarFour60female

Displaying from one class only

We will restrict the students of class four only
SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 AND class ='Four'
idnameclassmarksex
1John DeoFour75female
4Krish StarFour60female
5John MikeFour60female
21Babby JohnFour69female

Displaying from limited class using IN

Only from Class Four and Class Seven
SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 AND class IN('Four','Seven')
idnameclassmarksex
1John DeoFour75female
4Krish StarFour60female
5John MikeFour60female
21Babby JohnFour69female
34Gain ToeSeven69male

Displaying from limited class using NOT IN

SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 AND class NOT IN('Four','Seven')
idnameclassmarksex
18HonnyFive75male
20JacklyNine65female

Number of students from each class

How many number of students have secured mark between 60 and 75 in each class by using GROUP BY
SELECT class, COUNT(id) as no   FROM `student` WHERE mark BETWEEN 60 AND 75 GROUP BY class
classno
Five1
Four4
Nine1
Seven1
We can create a comparative statement in a grid view by using SQL IF and between command
SELECT class, 
SUM( IF( mark <50, 1, 0 ) ) AS grade_C, 
SUM( IF( mark BETWEEN 50 AND 70 , 1, 0 ) ) AS grade_B, 
SUM( IF( mark >70, 1, 0 ) ) AS grade_A 
FROM  `student` GROUP BY class
Out put is here
Classgrade_Cgrade_Bgrade_A
Eight 0 0 1
Five0 0 3
Four0 5 4
Nine1 1 0
Seven0 3 7
Six 0 1 6
Three 0 1 2

SQL IF condition

Sample code in PHP

Query using PHP script Here is the sample code in PHP to to get data using between query.

connect to database using PHP PDO.
<?Php
require "config.php";// Database connection

$count="SELECT * FROM `student`WHERE mark  BETWEEN 60 and 75";

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

Questions


Selecting records between two date ranges



Selecting records Case Condition Checking
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Sajib

    31-03-2010

    thanks. this is very helpful...
    alan

    08-11-2010

    so how would you have just the marks for id 4 and 5 display?
    Bas

    14-01-2011

    SELECT * FROM `student` WHERE id=4 OR id=5
    keerthana

    14-09-2011

    hi.. i need a mysql query for selecting transactions between two dates
    Sam in Kenya

    25-09-2011

    Hie keerthana: SELECT date from jobvacancies WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH)
    mebrahtu

    28-11-2011

    I have read (the SQL BETWEEN Command to fetch records from a range ) above. And it was very informative and helpful.thanks for who posted it.
    rasheed

    12-01-2012

    i need a query of sql to get ten top student from table on base of their marks plz help any one
    Jone

    18-01-2012

    @rasheed: SELECT * FROM Students ORDER BY Marks DESC LIMIT 10
    Azim

    20-02-2012

    how to use BETWEEN command using upper limit and lower limit exclusive
    reno rey quiza

    03-10-2012

    thanks a lot... this is really helpful... ! may you be returned a favor.. :))
    naveen

    27-10-2012

    i want only 50% marks in sql query
    PRAMOD

    01-01-2013

    TWO TABLES ARE GIVEN ,EMPLOYEES AND DEPARTMENT . DISPLAY THE NAMES OF ALL THE EMPLOYEES WHOSE SALARY IS NOT WITHIN THE RANGE FOR THE CORRESPONDING DEPARTMENT
    mahamad

    26-03-2013

    i want to do pagination in jsp so how i will be able to do that?plz help me ...
    Coder

    29-08-2013

    i want to get the supplier details from a table named supplier by giving supplier code and between two dates also without supplier code
    smo

    30-08-2013

    SELECT * FROM `supplier_table` WHERE dt BETWEEN 2005-01-01 AND 2005-12-31 and supplier_id=1234
    You can read more on query using two date ranges here.
    Priya

    15-07-2014

    I need a query which will select only those data whose length of data is within 10 to 12 and those length is greater or equal to 10, should start with 91 or 0
    omkar

    10-12-2014

    I need a query to count duplicate data between two dates.
    ravi

    25-02-2015

    i need to display three columns as name,count(range 5-10),count(range 6-10),
    the source table contain two columns i.e name and range

    example

    name range

    aaaa 5
    bbbb 9
    aaaa 6
    aaaa 7
    bbbb 9
    aaaa 5


    and i need output as

    name count(range 0-5) count(range 6-10 )
    aaaa 2 2
    bbbb 0 2
    Yusuf Ibrahim

    04-05-2015

    Please I need help in php and mysql, a code that will automatically generates position for a students according to their total marks.thank
    Chandu

    26-06-2015

    How to select only those rows whose sex is male....?
    smo1234

    27-06-2015

    You can use Order by to list students according to their mark.
    SHIV PANKAJ

    23-02-2016

    I have a table having record in one column like "2016-01-02 19:45:31.000".
    I have to select record with time between 19:40 and 19:50 for whole month.
    Kindly provide me the query.......
    chandu

    03-03-2016

    hi i need a query to fetch records for every 6 hrs interval in a day i.e., 4 times a day from a table
    smo1234

    10-08-2018

    Query using different time along with date like records between 9 to 18 hours of a particular date , all records with time 10 to 12 hours , all records group by hour etc are explained at DATE_SUB() and BETWEEN queries here.

    09-01-2023

    Informative ,thanks

    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