Mysql multiple limit in one sql statement

Question

I have a table in my database called "students" that contains the following columns (student_id, student_name, year_of_birth). and an array of years I'm trying to make one request that gets 10 students from a year in the array (years).

I could write

SELECT student_id FROM `students` WHERE year_of_birth=1950 LIMIT 10; SELECT student_id FROM `students` WHERE year_of_birth=1951 LIMIT 10; SELECT student_id FROM `students` WHERE year_of_birth=1952 LIMIT 10; SELECT student_id FROM `students` WHERE year_of_birth=1953 LIMIT 10; (and so on) 

But that would be very time consuming. Are there any other options Thank you

Answer 1

"select N rows for a group in MySQL": http://explainextended.com/2009/03/06/advanced-row-sampling/

Answer 2

If you are concerned that queries will return multiple result sets, you can throw UNION ALL between each SELECT :

 SELECT student_id FROM `students` WHERE year_of_birth=1950 LIMIT 10 UNION ALL SELECT student_id FROM `students` WHERE year_of_birth=1951 LIMIT 10 UNION ALL ... 

This, of course, can be combined with the alexn approach to generating a request from an array of years.

I don’t think this will give you much better performance than individual queries, but it can (in a future version of MySQL), since it gives the database engine a little more information about what you are doing.

Answer 3

Use a helper query that references a table:

 SELECT student_id FROM `students` AS s1 WHERE student_id IN (SELECT s2.student_id FROM `students` AS s2 WHERE s1.year_of_birth = s2.year_of_birth LIMIT 10) 

Only one problem: this will only work if you are using MySQL 5.1 or higher.

An alternative is to use the union statement:

 for ($year = 1950; $year < 2000; $year++) { $stmts[] = "SELECT student_id FROM `students` WHERE year_of_birth = $year LIMIT 10"; } $sql = implode(' UNION ALL ', $stmts; 

This will work for a wider range of MySQL versions.

Answer 4

This is also one example to find a leap year with several restrictions.

 select year_n from the_years select distinct month_n from the_months,the_years where year_n=$P{Year} (select distinct day_n from the_days,the_months where $P{Month} IN('Jan','Mar','May','Jul','Aug','Oct','Dec') limit 31) UNION ALL (select distinct day_n from the_days,the_months where $P{Month} IN('Apr','Jun','Sep','Nov') limit 30) UNION ALL (select distinct day_n from the_days,the_years,the_months where $P{Month}='Feb' and mod($P{Year},4)!=0 or mod($P{Year},100)=0 or mod($P{Year},400)=0 limit 28) UNION ALL (select distinct day_n from the_days,the_years,the_months where $P{Month}='Feb' and mod($P{Year},4)=0 and mod($P{Year},100)!=0 or mod($P{Year},400)=0 limit 29) 

Answer 5

why not just do it:

 $studentIds = array(1950, 1951, 1952, 1953); $sql = " SELECT student_id, year_of_birth FROM students WHERE student_id IN (" . implode(',', $studentIds) . ") "; $result = mysql_query($sql); $students = array(); while($row = mysql_fetch_assoc($result)) { $students[$row['year_of_birth']] = $row['student_id']; } 

Your $students array will contain arrays of student IDs with keys as birthdays.

Answer 6

You can try this,

 SELECT first.* from ( SELECT student_id FROM students WHERE year_of_birth=1950 LIMIT 0,10 ) AS first UNION SELECT second.* from ( SELECT student_id FROM 'students' WHERE year_of_birth=1951 LIMIT 10; )as second