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.
Share your thoughts in the comments
Please Login to comment...