How to Get Latest Updated Records in SQL?

SQL Server is a versatile database and it is the most used Relational Database that is used across many software industries. In this article, we will see how to get the latest updated records in SQL.

Step 1: Database creation

Command to create the database.  Here GEEKSFORGEEKS is the db name.

Query:

CREATE DATABASE GEEKSFORGEEKS;

Step 2: Make use of the create database.

USE GEEKSFORGEEKS;

Output:

• To have the latest updated records, we should have a column such as “last updated” with the “Timestamp” column in any table and when a record is newly inserted, it should get the current timestamp value for that column. This will help during record creation
• At the same time, we should have a trigger to update the value of the “last updated” column which will update the record with the latest timestamp.

Step 3: Table creation

CREATE TABLE [dbo].[AuthorsNew](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [AuthorName] [nvarchar](20) NULL,
    [Age] [int] NULL,
    [Skillsets] [nvarchar](max) NULL,
    [NumberOfPosts] [int] NULL,
    [isActiveAuthor] [bit] NULL,
    [lastUpdatedBy] DATETIME 
);

Step 4: Insertion of records in the table

INSERT INTO AuthorsNew (AuthorName,Age,Skillsets,
NumberOfPosts,isActiveAuthor,lastUpdatedBy)
VALUES ('Rachel',25,'Java,Python,.Net',
20,1,'2021-11-12:56:40');

INSERT INTO AuthorsNew (AuthorName,Age,Skillsets,
NumberOfPosts,isActiveAuthor,lastUpdatedBy)
VALUES ('Monica Geller',25,'Android,Python,.Net'
,15,1,'2021-11-15 14:53:32');

INSERT INTO AuthorsNew (AuthorName,Age,Skillsets,
NumberOfPosts,isActiveAuthor,lastUpdatedBy)
VALUES ('Phoebe',25,'IOS,GO,R',5,1,
'2021-11-07 12:56:40');

INSERT INTO AuthorsNew (AuthorName,Age,Skillsets,
NumberOfPosts,isActiveAuthor,lastUpdatedBy)
VALUES ('Chandler',25,'Java,Python,GO',15,1,
'2021-11-09 12:56:40');

INSERT INTO AuthorsNew (AuthorName,Age,Skillsets,
NumberOfPosts,isActiveAuthor,lastUpdatedBy)
VALUES ('Ross',25,'IOS,Android',20,1,
'2021-11-11 12:56:40');

INSERT INTO AuthorsNew (AuthorName,Age,Skillsets,
NumberOfPosts,isActiveAuthor,lastUpdatedBy)
VALUES ('Joey',25,'Android',3,0,'2021-11-15 
12:56:40');

INSERT INTO AuthorsNew (AuthorName,Age,Skillsets,
NumberOfPosts,isActiveAuthor,lastUpdatedBy)
VALUES ('Monica Geller',25,'Android,Python,.Net',
15,1,'2021-11-15 14:53:32');

Step 5: To fetch data from the AuthorsNew table.

Query:

SELECT * FROM AuthorsNew;

Output:

The “LastUpdatedBy” column is having the data at what time the data has got inserted/updated. First, get the date part alone from date-time.

Query:

SELECT CONVERT(VARCHAR(10), 
lastUpdatedBy, 111)
 from AuthorsNew;

To get the latest record:

Example1: Using TOP

Query:

SELECT TOP 1 * FROM AuthorsNew ORDER BY 
CONVERT(VARCHAR(10), lastUpdatedBy, 111) DESC

Output:

Example 2: Using MAX

Query:

SELECT * FROM AuthorsNew WHERE 
CONVERT(VARCHAR(10), lastUpdatedBy, 111) =
(SELECT MAX(CONVERT(VARCHAR(10), 
lastUpdatedBy, 111)) FROM AuthorsNew )

Output:

Example 3: Using DATEADD FUNCTION

In order to get a previous day, previous week, previous month, etc., data, we can use the DATEADD function.

Syntax:

SELECT column_name, ... FROM table_name
WHERE date_column >= DATEADD
day,-<n days>, GETDATE())

• The DATEADD() function subtracts n days from the current date. Here “day” specifies the number of days
• The obtained result is compared with the date column to get the result.

Suppose In order to get the data as on the current date or before 1 or 2 days for our table, we can perform the following. i.e. latest 2 days of data can be obtained by the below query.

Query:

SELECT * FROM AuthorsNew WHERE 
CONVERT(VARCHAR(10), lastUpdatedBy, 
111) >= DATEADD(day,-2, GETDATE());

Output:

The same DATEADD() function helps to get past month data also.

Syntax:

SELECT column_name, ... FROM table_name
WHERE date_column >= DATEADD
(MONTH,-<n months>, GETDATE())

i.e. instead of “day”, we need to put MONTH and get past 6 months data.

To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.

--Trigger that fires automatically
 whenever a update is done in a row
CREATE TRIGGER trigger_change_lastupdatedby
ON dbo.AuthorsNew
AFTER UPDATE
AS
UPDATE dbo.AuthorsNew
SET lastUpdatedBy = CURRENT_TIMESTAMP
WHERE id IN (SELECT DISTINCT id FROM INSERTED);

Output:

Now perform updates for ID = 3

Query:

UPDATE AuthorsNew SET AuthorName = 
'Monica Ross Geller' WHERE ID = 3;
--We can see that 'lastupdatedby'
 column also got changed because 
 of the trigger availability.

Output:

In this way also, we can able to have the record modifications and at any point in time, we can get the latest record by applying the above set of queries.

Example 4: Using IDENT_CURRENT()

IDENT_CURRENT() function  accepts the table name(AuthorsNew) and returns the last identity value generated for AuthorsNew table . By using this facility we can find the last inserted record.

Query:

SELECT * FROM dbo.AuthorsNew WHERE [id] = 
(SELECT IDENT_CURRENT('dbo.AuthorsNew'));

Output:

In the above examples, we have seen how to get the latest records as of today, last week, past month, and also the latest inserted /updated records for a sample table.