Select Nth record from MySQL query from Millions of rows

Question

I have a MySQL query as below; I would like to select the top record for each range of 600 records in a table with 1.8M records. So far I have to loop 3,000 times to accomplish this which is not an efficient solution.

Database Schema;

 Table: bet_perm_13predict
 id     bet_id      perm_id     avg_odd     avg_odd2    avg_odd3
 1      23          1           43.29       28.82       28.82
 2      23          2           42.86       28.59       28.59
 3      23          3           43.13       28.73       28.73


 Table: bet_permute_13games
 perm_id    perm_code
 1      0000000000000
 2      0000000000001
 3      0000000000002
 4      0000000000010

Sample MySQL Query in PHP

$totRange   = 0; //Used as starting point in rang
$range      = 600; //Used as range
$stop       = 0;//Used as endPoint of range

while($totRange < 1800000){
    $stop   = $totRange+$range;

    $sql = "SELECT (tb1.avg_odd2 + tb1.avg_odd3) AS totAvg_odd ,
    tb1.perm_id , tb1.avg_odd, tb1.avg_odd2, tb1.avg_odd3, tb2.perm_code 
    FROM bet_perm_13predict tb1 
    INNER JOIN bet_permute_13games tb2 ON tb2.perm_id = tb1.perm_id
    WHERE tb1.bet_id = '$bet_id' && tb1.perm_id 
    BETWEEN $startRange AND $stop ORDER BY totAvg_odd ASC LIMIT 1"

    $q1    = $this->db->query($sql);
    $totRange = $stop;
}

In other words I want to select a sample of the data that will represent the entire table with the sample not being random but predefined using the top record in range of 600. So far I have no idea how to proceed. There is no clear online material on this subject.

Can you please show us the table structure, some example data and what the expected output would be from that sample data? Your question doesn't provide us with enough information as is. You also have some unknown variables in your query, like `$startRange` and `$bet_id`. — M. Eriksson, Sep 24 '18 at 20:02
Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Sep 24 '18 at 20:02
what mysql version do you have? Do you have window functions? — Juan Carlos Oropeza, Sep 24 '18 at 20:06
I have added more information, the schemas & sample data. MySQL version 5.6.21 — jmsiox, Sep 24 '18 at 20:17
Instead of every group of 600 records, out 1.8m, let's pretend it's every group of 3 records, out of 12. With that in mind, see [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Sep 24 '18 at 21:48
Try without iteration. Add before query (or run as separated query) `SET @position := 0; ` then to end of query add `HAVING (@position := @position + 1) % 600 = 1;` — Karol Murawski, Sep 25 '18 at 00:01
@Karol Murawski this is close to what I want. I want to go a step further and not Just get the 600th record but get the Max() record for the range. — jmsiox, Sep 25 '18 at 12:22

score 1 · Answer 1 · answered Sep 24 '18 at 20:12

1

You can use integer division to create groups.

DEMO

SELECT ID, ID DIV 600 as grp
FROM Table1

Then find the max value on each group. Some options here

Get records with max value for each group of grouped SQL results

answered Sep 24 '18 at 20:12

Juan Carlos Oropeza

47,252
12
78
118

score 0 · Answer 2 · answered Sep 26 '18 at 15:04

For those who might encounter the same issue, this is how I solved it. I used @Juan Carlos suggestion and added a way to pick top record of group using Subquery.

SELECT * FROM 
         (SELECT * , perm_id DIV $limit as grp , (avg_odd2 + avg_odd3) AS totAvg_odd 
           FROM bet_perm_13predict WHERE bet_id = '$bet_id'  ORDER BY grp ASC ) tb1
INNER JOIN bet_permute_13games tb2 ON tb2.perm_id = tb1.perm_id
INNER JOIN bet_entry tb3 ON tb3.bet_id = tb1.bet_id
WHERE tb1.avg_odd2 < (SELECT AVG(avg_odd2) FROM bet_perm_13predict WHERE bet_id = '$bet_id' )
                     && tb1.avg_odd3 < (SELECT AVG(avg_odd3) FROM bet_perm_13predict WHERE bet_id = '$bet_id' )
GROUP BY grp ORDER BY totAvg_odd ASC
LIMIT 100

Select Nth record from MySQL query from Millions of rows

2 Answers2