Select The Last Row in a SQL Table

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

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 ;

How to read the last row with SQL Server

If you're using MS SQL, you can try:

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC 

SQL Select last row from row that have same column

You can use ROW_NUMBER() function to generate row number partitioned by tp_product then order by post_numb descending; with consideration that the largest post_numb will be the "latest". Make that as subquery and do a WHERE Rnum=1 to only return the ones being deemed as "latest":

SELECT ID, post, tp_product, post_numb
   FROM
(SELECT ID, post, tp_product, post_numb,
       ROW_NUMBER() OVER (PARTITION BY tp_product ORDER BY post_numb DESC) AS Rnum
    FROM tp_posts) v
    WHERE Rnum=1;

However, this only work with MySQL v8+ and MariaDB v10.2+.

Demo

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

SQL Selecting all BUT the last row in a table?

If Id is unique, you can do it as follows:

SELECT ...
FROM MyTable
WHERE Id < (SELECT MAX(Id) FROM MyTable)

Sql select last record with specific WHERE

ORDER BY the date field DESCENDING and keep only the first row returned with LIMIT 1

SELECT *
FROM login
WHERE user_id=1
ORDER BY date DESC
LIMIT 1

You get a syntax error in your query because your WHERE clause comes after your ORDER clause

How to select the last row from one table then update the last row of another one?

Assuming that id is unique in table1:

UPDATE table1 t1
    SET t1.col1 = (select t2.col2 from table2 t2 order by id desc limit 1) 
    ORDER BY t1.id DESC
    LIMIT 1;

This updates the "last" row in table1 (by id) with the "last" row in table2 (by id).

Your syntax doesn't work in multiple ways:

• MySQL does not support the FROM clause in UPDATE.
• MySQL does not allow you to reference the table being updated in a subquery.

---

Related Topics