Скачать или смотреть How to SELECT the Last 30 Rows of Data Using SQL

Learn how to effectively select the last 30 rows of data from a MySQL table in this comprehensive guide.
---
This video is based on the question https://stackoverflow.com/q/69564927/ asked by the user 'CAtcAT Chilling' ( https://stackoverflow.com/u/13790253/ ) and on the answer https://stackoverflow.com/a/69564971/ provided by the user 'Manusha' ( https://stackoverflow.com/u/5148990/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to SELECT data 30 last data from ID

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to SELECT the Last 30 Rows of Data Using SQL

When working with databases, especially MySQL, it's common to need a way to retrieve a specific number of recent records from a table. For example, you may want to select the last 30 entries in a table that uses an AUTO_INCREMENT integer as the primary key. In this guide, we will address a common problem: how to retrieve the last 30 rows from a table by using a specific SQL query.

The Problem

Suppose you have a table that records data once a day, and the primary key of that table is set to auto-increment. You might find yourself needing to fetch the last 30 entries from that table to display them meaningfully. The initial attempt might involve executing the following query:

[[See Video to Reveal this Text or Code Snippet]]

However, as you might have already experienced, this query returns all the data rather than just the last 30 rows you are targeting. So, what went wrong? Let's explore the solution step-by-step.

The Solution

Step 1: Understand the Current Query

The problem with your initial query arises because it is using a WHERE clause based on a range of IDs. While this would technically work if the IDs were contiguous and there were no gaps, it often returns more rows than intended, especially if records have been deleted or if IDs are unevenly spaced.

Step 2: Use an Alternative Query

To effectively fetch the last 30 rows based on the ID, you can take advantage of the ORDER BY clause combined with LIMIT. This approach ensures you're getting the most recent rows without making unsupported assumptions about the ID distribution.

Here is the correct SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Query:

*SELECT : This command tells SQL to select all columns from the specified table.

FROM tbl_name: You will replace tbl_name with the actual name of your table.

ORDER BY id DESC: This part of the command orders the results by the id column in descending order (from highest to lowest), ensuring that the most recently added entries come first.

LIMIT 30: This limits the output to only the last 30 rows according to the ordering.

Step 3: Run the Query

Run the above query in your SQL environment. If executed correctly, you should now see only the last 30 entries from your table, which is exactly what you were looking for.

Conclusion

Retrieving recent data from a MySQL table doesn't have to be complicated. By understanding how to properly utilize ORDER BY with LIMIT, you can effectively select the latest rows without encountering unwanted results. This approach not only simplifies querying but also enhances performance by reducing the data processed.

If you're just starting out with SQL or looking to improve your database skills, mastering these fundamental queries will equip you with the tools needed to manage data effectively. Happy querying!