MySQL: Limit by a group of rows, not by a row <p>I have a MySQL table like the following. The <code>id</code> and <code>name</code> fields are just here to help identify the row, <code>l</code> represents many other fields whit not relevant data, and <code>lat</code> and <code>lng</code> are used to find duplicated entries.</p> <pre><code>CREATE TABLE v (`id` int, `lat` int, `lng` int, `l` varchar(3), `name` varchar(8)) ; INSERT INTO v (`id`, `lat`, `lng`, `l`, `name`) VALUES ( 1, 12, 12, 'a', 'group1-1'), ( 2, 12, 12, 'b', 'group1-2'), ( 3, 13, 12, 'c', 'single1'), ( 4, 13, 13, 'd', 'group2-1'), ( 5, 13, 13, 'e', 'group2-2'), ( 6, 13, 13, 'f', 'group2-3'), ( 7, 10, 13, 'g', 'group3-1'), ( 8, 10, 13, 'h', 'group3-2'), ( 9, 11, 12, 'h', 'group4-1'), (10, 11, 12, 'h', 'group4-2'), (11, 10, 14, 'i', 'group5-1'), (12, 10, 14, 'j', 'group5-2'), (13, 10, 14, 'j', 'group5-3') ; </code></pre> <p>Now I want to get all rows where there is more than one row with the same lat and lng values (let's call that a group):</p> <pre><code>SELECT v.* FROM v INNER JOIN ( SELECT lat, lng FROM v GROUP BY lat, lng HAVING COUNT(*) &gt; 1 ) AS vg USING (lat, lng) ORDER BY name ; </code></pre> <p>Outputs:</p> <pre><code>ID LAT LNG L NAME 1 12 12 a group1-1 2 12 12 b group1-2 4 13 13 d group2-1 5 13 13 e group2-2 6 13 13 f group2-3 7 10 13 g group3-1 8 10 13 h group3-2 9 11 12 h group4-1 10 11 12 h group4-2 11 10 14 i group5-1 12 10 14 j group5-2 13 10 14 j group5-3 </code></pre> <p>Works well so far, all but the "single1" rows are selected and ordered by group.</p> <p>Now I want to add a limit to form different pages of this data. But all rows of the same group need to be on the same page (but not one page per group and it doesn't matter how many rows actually are on one page - just not too many). I don't see this done with a LIMIT statement, so I started to write my own:</p> <pre><code>SET @grp := 0, @last_lat := '', @last_lng := ''; SELECT * FROM ( SELECT v.*, @grp := IF((@last_lat = lat) &amp;&amp; (@last_lng = lng), @grp, @grp + 1) AS grp, @last_lat := lat AS llat, @last_lng := lng AS llng FROM v INNER JOIN ( SELECT lat, lng FROM v GROUP BY lat, lng HAVING COUNT(*) &gt; 1 ) AS vg USING (lat, lng) ORDER BY lat, lng ) AS vv WHERE grp BETWEEN 0 AND 7 ORDER BY grp ; </code></pre> <p>This query whit out the WHERE part outputs this data:</p> <pre><code>ID LAT LNG L NAME GRP LLAT LLNG 1 12 12 a group1-1 6 12 12 2 12 12 b group1-2 6 12 12 5 13 13 e group2-2 7 13 13 6 13 13 f group2-3 7 13 13 4 13 13 d group2-1 7 13 13 7 10 13 g group3-1 8 10 13 8 10 13 h group3-2 8 10 13 10 11 12 h group4-2 9 11 12 9 11 12 h group4-1 9 11 12 13 10 14 j group5-3 10 10 14 11 10 14 i group5-1 10 10 14 12 10 14 j group5-2 10 10 14 </code></pre> <p>The idea is to change the values in the line WHERE grp BETWEEN 0 AND 7 for each page. But the numbers from the group don't start at 1 and I can't see why (and that makes it unusable for the first page). The Start number is different depending on how many group are in the whole data. So what I need to have, is that the grp column start whit 1 and then continues whit always +1.</p> <p>What is wrong with this query and are there any better ways to do this?</p> <p>You can play whit this query here: <a href="http://sqlfiddle.com/#!8/86d3c/2" rel="nofollow">http://sqlfiddle.com/#!8/86d3c/2</a></p>