2

If I specify a number, say 5, what query will give me all the rows after the 5th row? Like,

SELECT * FROM table WHERE 1=1;

only I want it to exclude the top 5. Thanks.

mathon12
  • 161
  • 4
  • 15

4 Answers4

7

Use the limit with a very high number as the second argument.

select * from myTable limit 5,18446744073709551615;

From MySQL Docs:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

BTW: You don't need WHERE 1=1. It doesn't add any value to the query, just clutter.

Community
  • 1
  • 1
Asaph
  • 147,774
  • 24
  • 184
  • 187
  • Yeah, that WHERE was just placeholder. This should do it. Thanks! – mathon12 Dec 18 '09 at 01:01
  • huh! where will you store the result of the above query? Isn't it more logical to fetch a limited set of rows (whatever is optimal for the memory available on your system), do some operation and fetch a next set of rows and so on...? Not saying your answer is wrong, just saying that everything that MySQL docs say is not a solution, more often that not it's just an answer to a question. Solutions are supposed to keep performance and feasibility into account. BTW, *"some large number"* in docs is `(2^64)-1` which is max of `(unsigned) bigint` – Fr0zenFyr Sep 17 '18 at 05:01
  • @Fr0zenFyr "...that everything that MySQL docs say is not a solution, more often that not it's just an answer to a question" -- Not in this case. The quoted section of my answer is part of the [official MySQL Docs](http://dev.mysql.com/doc/refman/5.0/en/select.html), *not* the user contributed comments section below it. – Asaph Sep 17 '18 at 17:20
  • I din't deny that the quote is from official docs. All I was trying to say was that the statement in docs is very general and doesn't take the possible memory overflow into account. Say for example, the result of a query gives `> 2^20` results. My comment simply was pointing at this caveat, nothing about your answer in specific. I also mentioned in previous comment that such issue can be addressed by looping, for ex, by getting up to `2^15` results at a time (or less), process it and get next set and so on.. – Fr0zenFyr Sep 17 '18 at 17:32
5
SELECT * FROM table ORDER BY somecolumn LIMIT 5,1000

http://dev.mysql.com/doc/refman/5.1/en/select.html

[LIMIT {[offset,] row_count | row_count OFFSET offset}]
gahooa
  • 114,573
  • 12
  • 89
  • 95
  • This query won't retrieve anything past row 1000. What if the table contains more than 1000 rows? – Asaph Dec 18 '09 at 00:54
  • @Asaph: how many rows do you want to retrieve? I included specification of the LIMIT syntax - if this isn't obvious, then perhaps one should move to a different field? – gahooa Dec 18 '09 at 16:41
0

Are you looking for LIMIT?

SELECT * FROM table LIMIT 5,9999999

The second parameter to limit is just a large number to get all rows. Adjust accordingly.

See: http://dev.mysql.com/doc/refman/5.5/en/select.html

ZoogieZork
  • 10,947
  • 5
  • 43
  • 42
0

If you have a column whose values can be ordered (and are unique), say ID1, You can do it in pure SQL (e.g. not using the MySQL specific LIMIT) as follows (Syntax is Sybas-ey, may need to tweak table alias and joins to work on mySQL):

SELECT * FROM table WHERE ID1 not in
-- SELECT FIRST @N ROWS IN ACCENDNING ORDER
(SELECT t1.ID1 FROM table 't1', table 't2' 
 WHERE t1.ID1 < t2.ID2
 GROUP BY t1.ID1
 HAVING count(*) <= @N)
DVK
  • 119,765
  • 29
  • 201
  • 317
  • Interesting answer. However, with MySQL un-optimization of sub queries, you are likely to give the CPU a heart attack. – gahooa Dec 18 '09 at 16:43
  • OK, so stick the IDs from sub-query into a temp table for the unoptimized databases :) – DVK Dec 19 '09 at 00:48