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

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

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

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
Selecting records between two date ranges

Number of User Comments : 21


Google+

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 and 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....?
subhendu

27-06-2015

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

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






OPINION POLL

How you handle Search Engine Optimization ( SEO ) for your website
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer