CODEWITHSUNDEEP
mysqlsqlselect
Ameer
Ameer

Reputation: 600

How to select particular range of values in MySQL table?

The following is my MySQL table. I want to select a particular range of values from my table hello.

name      age        job       gender

A         33         dgfd      m
b         44         gdfg      f
c         21         jhkh      m
e         23         etertr    m

How would I select a male whose age falls into the age category of 20-30 years.

SELECT hello.*
WHERE hello.age='20-30' AND hello.gender='m';

Upvotes: 6

Views: 34373

Answers (4)

AbsoluteƵERØ
AbsoluteƵERØ

Reputation: 7880

Because ages change from year-to-year you can do this.

Setup the table like this:

delimiter $$

CREATE TABLE `hello` (
  `name` varchar(45) NOT NULL,
  `birthdate` date DEFAULT NULL,
  `job` varchar(45) DEFAULT NULL,
  `gender` enum('m','f') DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$

Values I'm using:

'A', '1980-08-04', 'clerk', 'm'
'B', '1969-10-12', 'waitress', 'f'
'C', '1992-09-16', 'pilot', 'm'
'd', '1991-02-21', 'unemployed', 'm'

SQL query:

select name,TIMESTAMPDIFF(YEAR,birthdate,current_date) as age,job,gender from hello where birthdate > current_date - interval 30 YEAR and birthdate < current_date - interval 20 year;

Returned answers to the query

name    age     job          gender
C       20      pilot        m
d       22      unemployed   m

Added to SQLFiddle here. http://www.sqlfiddle.com/#!2/0143c/1/0

Upvotes: 0

Kermit
Kermit

Reputation: 34063

SELECT name
FROM hello
WHERE age BETWEEN 20 AND 30
  AND gender = 'm'

Don't store age. Store a date field and calculate the age. What would happen if the person got older?

Upvotes: 13

Niro
Niro

Reputation: 776

SELECT * FROM hello WHERE age>=20 AND age <=30 AND gender='m';

Upvotes: 0

Taryn
Taryn

Reputation: 247880

You can use a WHERE clause to filter the data:

select name, age, job, gender
from hello
where age >=20
  and age <=30
  and gender = 'm'

See SQL Fiddle with Demo

This can also be written using BETWEEN:

select name, age, job, gender
from hello
where age between 20 and 30
  and gender = 'm'

See SQL Fiddle with Demo.

Typically you will want to store a date of birth instead of the age of a person, then the age can be calculated when needed.

Upvotes: 18

Related Questions