How to Select the Last 10 Rows of an SQL Table Which Has No Id Field

How to SELECT the last 10 rows of an SQL table which has no ID field?

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?

SQL get last rows in table WITHOUT primary ID

I assume that when you are saying 'last rows', you mean 'last created rows'.

Even if you had primary key, it would still be not the best option to use it do determine rows creation order.
There is no guarantee that that the row with the bigger primary key value was created after the row with a smaller primary key value.

Even if primary key is on identity column, you can still always override identity values on insert by using
set identity_insert on.

It is a better idea to have timestamp column, for example CreatedDateTime with a default constraint.
You would have index on this field.
Then your query would be simple, efficient and correct:

select top 1 *
from MyTable
order by CreatedDateTime desc

If you don't have timestamp column, you can't determine 'last rows'.

SQL Server SELECT LAST N Rows

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

How to select the last record of a table in SQL?

Without any further information, which Database etc the best we can do is something like

Sql Server

SELECT TOP 1 * FROM Table ORDER BY ID DESC

MySql

SELECT * FROM Table ORDER BY ID DESC LIMIT 1

SELECT last 250 rows from a table with no auto id

Try this:

SELECT col1, col2, ... 
FROM (SELECT col1,col2, ..., (@auto:=@auto+1) indx 
      FROM tablename, (SELECT @auto:=1) AS a
     ) AS b 
ORDER BY indx DESC 
LIMIT 30

How to select the last record of each ID

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;

Select last N rows from MySQL

You can do it with a sub-query:

SELECT * FROM
(
 SELECT * FROM table ORDER BY id DESC LIMIT 50
) AS sub
ORDER BY id ASC;

This will select the last 50 rows from table, and then order them in ascending order.

SQL - Select first 10 rows only?

In SQL server, use:

select top 10 ...

e.g.

select top 100 * from myTable
select top 100 colA, colB from myTable

In MySQL, use:

select ... order by num desc limit 10

---

Related Topics