How to Select the Last Record of a Table in 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

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 ;

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 latest record of each month?

In Teradata, you might find trunc() to be a simple method:

select a.id, a.name, a.number, a.date
from (select a.*,
             row_number() over (partition by trunc(date, 'MON') order by date desc) as seqnum
      from tableA a
     ) a
where seqnum = 1;

Teradata also supports qualify:

select a.id, a.name, a.number, a.date
from tableA a
qualify row_number() over (partition by trunc(date, 'MON') order by date desc) = 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 

Select last record of each table on database

Since the last record will have highest id ,

 SELECT * FROM `yourtable` WHERE `id` = (select max(id) from  `yourtable`);

Use can use order by clause also

SELECT * FROM `yourtable` ORDER BY id  DESC LIMIT 1;

For getting all table entry

SELECT * FROM `firsttable` WHERE `id` = (select max(id) from  `firsttable`)
UNION ALL
SELECT * FROM `secondtable` WHERE `id` = (select max(id) from  `secondtable`) 
UNION ALL
SELECT * FROM `thirdtable` WHERE `id` = (select max(id) from  `thirdtable`)
.
.
.
UNION ALL
SELECT * FROM `lasttable` WHERE `id` = (select max(id) from  `lasttable`);          

Union all Will ensure that same entry are listed for different table

Edit2

<?php

  $sql = 'show tables';
  $result = mysql_query($sql);
  $numofrows = mysql_num_rows($result);

    $sqlQuery = '';
    $i=0;
    while($d = mysql_fetch_array($result)){
        $i++;

        if($i==$numofrows){
          $sqlQuery .= "SELECT * FROM `".$d[0]."` WHERE `id` = (select max(id) from  `".$d[0]."`)" ;
        }else{
          $sqlQuery .= "SELECT * FROM `".$d[0]."` WHERE `id` = (select max(id) from  `".$d[0]."`) UNION ALL " ;
        }
    }

    echo $sqlQuery; //your Sql Query

?>

---

Related Topics