How to SELECT the last 10 rows of an SQL table which has no ID field?_问答_开发者

I have an MySQL table with 25000 rows.

This is an imported CSV file so I want to look at the last ten rows to make sure it imported everything.

However, since there is no ID column, I can't say:

SELECT * FROM big_table ORDER BY id DESC

What SQL statement would show me the last 10 rows of this table?

The structure of the table is simply this:

columns are: A, B, C, D, ..., AA, AB, AC, ... (like Excel)
all fields are 开发者_如何学JAVAof type TEXT

All the answers here are better, but just in case... There is a way of getting 10 last added records. (thou this is quite unreliable :) ) still you can do something like

SELECT * FROM table LIMIT 10 OFFSET N-10

N - should be the total amount of rows in the table (SELECT count(*) FROM table). You can put it in a single query using prepared queries but I'll not get into that.

Select from the table, use the ORDER BY __ DESC to sort in reverse order, then limit your results to 10.

SELECT * FROM big_table ORDER BY A DESC LIMIT 10

SQL tables have no implicit ordering, the order has to come from the data. Perhaps you should add a field to your table (e.g. an int counter) and re-import the data.

However that will only give the order of the import and not the data. If your data has no ordering you have to find out how to add it.

EDIT: you say

...to make sure it imported everything.

What's wrong with using row count?

You can use the "ORDER BY DESC" option, then put it back in the original order:

(SELECT * FROM tablename ORDER BY id DESC LIMIT 10) ORDER BY id;

SELECT * FROM big_table ORDER BY A DESC LIMIT 10

If you're doing a LOAD DATA INFILE 'myfile.csv' operation, the easiest way to see if all the lines went in is to check show warnings(); If you load the data into an empty or temporary table, you can also check the number of rows that it has after the insert.

If you have not tried the following command

SELECT TOP 10 * FROM big_table ORDER BY id DESC;

I see it's working when I execute the command

SELECT TOP 10 * FROM Customers ORDER BY CustomerId DESC;

in the Try it yourself command window of https://www.w3schools.com/sql/sql_func_last.asp

executing a count(*) query on big data is expensive. i think using "SELECT * FROM table ORDER BY id DESC LIMIT n" where n is your number of rows per page is better and lighter

That can be done using the limit function, this might not seem new but i have added something.The code should go:

SELECT * FROM table_name LIMIT 100,10;

for the above case assume that you have 110 rows from the table and you want to select the last ten, 100 is the row you want to start to print(if you are to print), and ten shows how many rows you want to pick from the table. For a more precised way you can start by selecting all the rows you want to print out and then you grab the last row id if you have an id column(i recommend you put one) then subtract ten from the last id number and that will be where you want to start, this will make your program to function autonomously and for any number of rows, but if you write the value directly i think you will have to change the code every time data is inserted into your table.I think this helps.Pax et Bonum.

you can with code select 10 row from end of table. select * from (SELECT * FROM table1 order by id desc LIMIT 10) as table2 order by id"

If you know how many rows to expect, I would create a separate temporary table in your database of the expected structure, append into that, then check the count... Once you are good with that, then you can massage that data before appending it into your final production table.

A low-tech approach: Doing this with SQL might be overkill. According to your question you just need to do a one-time verification of the import.

Why not just do: SELECT * FROM ImportTable

and then scroll to the bottom of the results grid and visually verify the "last" few lines.

If you want to retrieve last 10 records from sql use LIMIT. Suppose the data base contains 20 records.Use the below query