15834

Is it possible to use LIMIT with a subquery result?

Question:

When the last rows of an ordered set are required, it is usual to create a derived table and reorder. For example, to return the last 3 elements of a table with an auto incremented id:

SELECT * FROM ( SELECT * FROM table ORDER BY id DESC LIMIT 3 ) t ORDER BY t.id

Since LIMIT can also have an offset, the same result could be achieved if we know the number of lines (say 10) in advance with this query:

SELECT * FROM table ORDER BY id LIMIT 3 OFFSET 7

Is it possible to run a subquery to count(*) a table and dynamically build a LIMIT using that number?

SELECT * FROM table ORDER BY id LIMIT 3 OFFSET [ select count() -3 ]

Answer1:

No, it isn't possible to specify a dynamic offset.

Your original query with a subquery is the easiest way to do this.

Recommend

  • add/remove multiple input fields
  • Function calling incorrect values
  • is there a way in autoit script to find if current file was included or it is running on its own?
  • How to skip require in ruby?
  • Hibernate to update table schema
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • Limiting recursion to certain level - Duplicate rows
  • Jackson Parser: ignore deserializing for type mismatch
  • Record samples being played with OpenAL
  • Breeze - Deleted Items nav properties bug
  • Change multiple background-images with jQuery
  • Using $this when not in object context
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • javaw.exe and eclipse startup problems
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • Incrementing object id automatically JS constructor (static method and variable)
  • Trying to switch camera back to front but getting exception
  • When should I choose bucket sort over other sorting algorithms?
  • How to limit post in wp_query
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How to set the response of a form post action to a iframe source?
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • Change div Background jquery
  • How to stop GridView from loading again when I press back button?
  • need help with bizarre java.net.HttpURLConnection behavior
  • How does Linux kernel interrupt the application?
  • Qt: Run a script BEFORE make
  • Django query for large number of relationships
  • reshape alternating columns in less time and using less memory
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?