How to Select the Latest Record in SQL Server?

A week ago, I got the requirement from our client to select the latest record from the SQL table. I did it through SQL query and took the output.

This tutorial will discuss simple and easy ways to select the newest record in SQL Server. Also, I will explain how to get maximum and minimum records, top values, and the last 1000 records as well.

Table of Contents

SQL Table

Before fetching the latest record, here my reference table is EmployeeInfo. You can see all the column details in the table.

select * from EmployeeInfo

Select the Latest/Last Column in the SQL Server

Here, I want to see the latest or last record in the table. Use the code below. We have selected two columns from the table shown at the beginning of the post. We have specified TOP 1 to limit the number of results to one.

SELECT TOP 1 Employeename, Employeecountry FROM EmployeeInfo 
ORDER BY EmployeeId DESC;

Select all the Latest/ Last Column in SQL Server

Before, we saw how to fetch the latest record in one column. Now, we will see how to get the record in all the columns present in the table.

SELECT TOP 1 * FROM EmployeeInfo  
ORDER BY [EmployeeId] DESC; 
GO

By using *, we have taken all the last records in the table.

Top 2 Records in SQL Table

Here, I want to see the top 2 country names in the EmployeeInfo table. Below is the syntax.

Select top 2 Employeecountry From EmployeeInfo
Order By EmployeeId

Now, we will see how to get the records using multiple columns in SQL Server.

Select top 2 Employeecountry,Employeename From EmployeeInfo
Order By EmployeeId

Select Maximum Record in SQL

Sometimes, the user faces a situation like need to fetch the record with the highest value. However, finding a single record manually from many records will be quite difficult.

To do so, we should use the WHERE Clause and MAX() in our query. The SQL Server MAX() function finds the highest value from a column. The WHERE clause filters the records where the column’s value is maximum. Use the syntax below to get maximum values.

SELECT column_name, ... FROM table_name 

WHERE column_name = (SELECT MAX(column_name) FROM table_name)

Here I have a table named EmployeeInfo where I want to take a maximum record for the column Employeesalary and see the output.

SELECT Employeesalary FROM EmployeeInfo

WHERE Employeesalary = (SELECT MAX(Employeesalary) FROM EmployeeInfo)

There is a maximum salary of 65000 with 2 records, so it is reflected in the output.

Select Minimum Record in SQL Server

In the previous step, we saw how to select maximum records in a table from SQL Server. Let’s see how to choose a record with a minimum value.

The MIN() function only needs to be used instead of the MAX() function. Additionally, we can remove records with the lowest column value by using this function in the WHERE clause.

Use the below syntax to get minimum values from the table.

SELECT column_name, ... FROM table_name 
WHERE column_name = (SELECT MIN(column_name) FROM table_name)

We will see for the table EmployeeInfo. And the output is below.

SELECT Employeesalary FROM EmployeeInfo

WHERE Employeesalary = (SELECT MIN(Employeesalary) FROM EmployeeInfo)

Use Row_number() to Get the Last Record

We can use row_number() to get the record according to user requirements. Here, I want to get the last Employeename in the table. Use the syntax below.

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY Employeename DESC) AS rn
  FROM EmployeeInfo
) AS subquery
WHERE rn = 1;

By doing this, you can select the last Employeename in the Table.

Select First and Last Record in SQL Server

So far, we have studied how to retrieve an SQL Server table’s most recent record. Now, let’s learn how to choose a table’s first and last records simultaneously.

Selecting a table’s first and last record can be done differently.

First, use the SELECT statement to select the first record from a table.
Again, use another SELECT statement to get the last record from the table.
Finally, combine both the results using the UNION operator.

SELECT * FROM ( SELECT TOP 1 * FROM EmployeeInfo
                ORDER BY [EmployeeId]) first
UNION ALL  
SELECT * FROM  ( SELECT TOP 1 * FROM  EmployeeInfo
               ORDER BY [EmployeeId] Desc) last

First and last latest record in SQL Server

Select the Last Modified record in SQL Server

First, we should have a last modified column in SQL Server that will automatically have a DateTime value based upon the last modification. And then, we can easily find the last updated record either by arranging the records or by finding records with the highest modified date.

But, first, we have to modify our sample table and add a new column named LastModified which will hold the DateTime value. And we are going to execute the following script to implement this task

ALTER TABLE EmployeeInfo
ADD [LastModified] datetime DEFAULT CURRENT_TIMESTAMP

Next, we are going to execute the following code to create the trigger.

CREATE TRIGGER trg_UpdateLastModified
ON EmployeeInfo
AFTER UPDATE
AS

UPDATE EmployeeInfo
SET LastModified = CURRENT_TIMESTAMP
WHERE EmployeeId IN (SELECT DISTINCT EmployeeId FROM INSERTED);

UPDATE EmployeeInfo
SET [Employeecountry] = 'Dubai' WHERE [Employeename] = 'David'

We can use the following query to find the last modified record in the sample table.

SELECT * FROM  EmployeeInfo
WHERE [LastModified] = (SELECT MAX([LastModified]) FROM EmployeeInfo)

Now, we can see the last modified column in the output.

Select the Last 1000 rows in the SQL Server

With SQL Server, we can use the TOP statement to limit the number of rows that are provided in the result. The rows can then be arranged so that the rows from the bottom appear at the top in descending order.

Here is a general syntax that we can use to get the last 1000 rows from the table.

SELECT TOP 1000 * FROM table_name 
ORDER BY identity_column DESC

Select Latest Date Record in SQL Table

This method is very simple in SQL Server. To do so, we need to arrange the date records of the table in descending order. So, the record with the latest date value will come at the top, and the record with the oldest date will come at the bottom.

If you want to select the latest date record, here is the syntax for getting the latest date records in SQL Server.

Select column_name, .. From table_name 
Order By date_column Desc;

Here I have a table name called smart product, and below the output. I need to see the order date for the product.

Select * From smartproduct
Order By [order_date] Desc;

How to Get Last Week Data in SQL Server

In SQL Server, we do not have any direct function that will return last week’s data from the SQL Server table. Still, there is a simple approach that we can implement to get the desired data.

For this implementation, we have to use the DATEADD() function in the WHERE clause of the statement. The DATEADD() function is used to add the date or time value to a specified date value and then return the result.

We can use the DATEADD() function in the WHERE to specify the date condition after which we want the result. The syntax for this implementation is given below.

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

In the syntax, we are using the DATEADD() function in the WHERE clause to filter the records having dates from last week. The DATEADD() function subtracts 7 days from the current date, and then the result is compared with the date column to get the result.

Let’s understand this implementation by executing a small example. For this, we will insert some data in the sample table with dates from the previous week.

INSERT INTO dbo.SampleTable
VALUES('Grady', 'Maddern', 'Male', 'Indonesia', '2021-07-15'),
      ('Maud', 'Antoni', 'Female', 'Russia', '2021-07-17');

Next, let’s use the given syntax to find last week’s records from the sample table.

SELECT * FROM SampleTable
WHERE [Date] >= DATEADD(day,-7, GETDATE())

The above query will return all the records where the date values in the “Date” column are from the previous week.

How to Get Last 6 Months’ Data in SQL Server

We can also use the DATEADD() function to get the last 6 months’ data from a table by comparing the dates in the WHERE clause.

Here is the syntax that we can use to get the last 6 months’ data from the table.

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

The DATEADD() function in the syntax subtracts 6 months from the current date and returns the result. The result from DATEADD() is then compared with the date column to get the result.

Let’s use the given syntax to find 6 months’ records from the sample table.

SELECT * FROM SampleTable
WHERE [Date] >= DATEADD(MONTH,-6, GETDATE())

After successfully executing the above example, we will get all the records from the last six months.

How to get last 6 months data in SQL Server

Conclusion

Now, you have some ideas about getting the latest records in SQL Server. This tutorial also covered minimum, maximum records, and the last 1000 records in the table as well.