mysql multiple limit in one sql statement

Question

I have table in my DB called "students" contains the following columns (student_id, student_name, year_of_birth).and array of years I trying to make one query that gets 10 student_id of each year in (years) array.

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

If you concern is that the queries will return multiple result sets, you could throw a UNION ALL in 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 can of course be combined with alexn's approach of generating the query from an array of years.

I do not think this will give you much better performance than seperate queries, but it might (in a future version of MySQL) since it gives the database engine a little more information about what you are doing.

Answer 2

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

Answer 3

Use a sub-query that links back to the 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 though: this will only work if you use MySQL 5.1 or higher.

The alternative is to use a 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

Just a shot in the dark, but would this work?

SELECT student_id FROM `students` WHERE (year_of_birth=1950 LIMIT 10) OR (year_of_birth=1951 LIMIT 10) OR (year_of_birth=1952 LIMIT 10) OR etc...