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

c1h4nd · Accepted Answer · 2010-01-17 15:07:45Z

7

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

answered Jan 17, 2010 at 15:07

c1h4nd

1914 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jørn Schou-Rode · Accepted Answer · 2010-01-17 14:47:45Z

2

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.

answered Jan 17, 2010 at 14:47

Jørn Schou-Rode

38.5k16 gold badges91 silver badges123 bronze badges

Comments

Matijs · Accepted Answer · 2010-01-17 15:35:52Z

1

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.

answered Jan 17, 2010 at 15:35

Matijs

2,5531 gold badge20 silver badges24 bronze badges

Comments

Tisho · Accepted Answer · 2012-07-18 21:48:03Z

1

This is also one of the example to find leap year with multiple limits

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)

edited Jul 18, 2012 at 21:48

Tisho

8,5426 gold badges46 silver badges52 bronze badges

answered Jul 18, 2012 at 20:42

Ansul Gupta

514 bronze badges

Comments

alexg · Accepted Answer · 2013-11-28 16:31:19Z

0

why not simply do that:

$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 the keys as the years of birth.

answered Nov 28, 2013 at 16:31

alexg

92213 silver badges39 bronze badges

1 Comment

alexg Over a year ago

if you're more concerned about the limit, you can limit it in php, with a counter.

Crysis · Accepted Answer · 2019-08-23 10:31:21Z

0

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

answered Aug 23, 2019 at 10:31

Crysis

4162 gold badges7 silver badges28 bronze badges

Collectives™ on Stack Overflow

mysql multiple limit in one sql statement

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related