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.