How to select latest record in SQL Server

In this SQL Server tutorial, we will discuss How to select records from a SQL Server Table, we will also cover Different scenarios on selecting records in SQL Server and will cover the following topic:

• How to select latest record in SQL Server
• How to select last record in SQL Server
• How to select max record in SQL Server
• How to select min record in SQL Server
• How to select latest date records in SQL Server
• How to select last inserted record in SQL Server
• How to get last updated record in SQL Server
• How to select last 10 records in SQL Server
• How to select first and last record in SQL Server
• How to select max date record in SQL Server
• How to select last 1000 rows in SQL Server
• How to get last week data in SQL Server
• How to get last 6 months data in SQL Server

In this article, we are going to discuss some examples of How to select a particular record from a SQL Server table. Now, for the demonstration, we have to consider a table having some pre-populated data.

So, for most of the examples illustrated in this article, we are taking a table having 10 sample records. The sample table is given below.

How to select latest/last record in SQL Server

In SQL Server, when we insert a record in a table without specifying any condition then, the record is added in the last of that table. So, we can also say that the latest record is the last record in a table.

So, in this section, we will learn how to select the last or latest record in the SQL Server table.

Now, there can be two scenarios, first, when we only need the last value from a column, and second, when we need the last inserted row. We will discuss the solution of both scenarios.

How to select latest/last column in SQL Server

To select the last value from the table column, we can follow the given approach.

• First, select a column and restrict the number of results to only 1.
• Next, order the output in descending order so that we can get the last record at the top.

Now, to implement this approach, we can use the following syntax.

SELECT TOP 1 column_name FROM table_name  
ORDER BY column_name DESC; 

In the above syntax, we are using the SELECT statement to select the column. And we have also specified “TOP 1” which will limit the result to 1. Next, we are using the ORDER BY clause to arrange the result in descending order.

For the demonstration of this concept, consider the following example given below.

USE sqlserverguides
GO

SELECT TOP 1 [first_name], [gender], [country] FROM dbo.SampleTable  
ORDER BY [id] DESC; 
GO

In the example, we have selected 3 columns from the sample table shown at the starting of the post. And we have specified “TOP 1” to limit the number of results to one. After this, we are arranging the value of the table in descending order. We have ordered the table using a unique column id. And after successful execution of the query, we will get the following output.

So, from the output, we can observe that the query has returned the last record from the table. The last record represents first_name as “Leontine“, gender as “Male“, and the country as “New Zealand“.

Read: Latest Record for Each User in SQL Server

How to select latest/last row in SQL Server

The approach to select the last row will be the same as mentioned in the previous topic. The only thing that needs to be modified is the column name. We need to specify all the column names instead of one column or use “*” to select all columns.

Here is the syntax that we can use to implement the above task.

SELECT TOP 1 * FROM table_name
ORDER BY column_name DESC; 
GO

Next, let’s implement the following query on our sample table.

USE sqlserverguides
GO

SELECT TOP 1 * FROM dbo.SampleTable  
ORDER BY [id] DESC; 
GO

And after executing the above example, we will get the complete data of the last row.

So, from the output, we can observe that the query has returned the last record from the table. The last record represents first_name as “Leontine“, last_name as “Shevlan“, gender as “Male“, and the country as “New Zealand“.

Read SQL Server stored procedure vs function

How to select max record in SQL Server

While working as a DBA, you might get into a situation where you need to find the record with the highest value. Now, finding a single record with the highest value manually from thousand of records will be difficult.

So, in this section, we will discuss a method to find a record with maximum value in SQL Server.

Before going through the approach, consider the following sales table. We are going to use this sales table further in our example.

To select the recorded with the highest value, we have to use the WHERE clause and MAX() function in our query. The SQL Server MAX() function is used to find the highest value from a column. And the WHERE clause will help to filter the records where the value of the column is maximum. Here is the standard syntax that we can follow.

SELECT column_name, ... FROM table_name 

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

The above syntax will return the selected column value from a table where the column value is maximum. Now, let’s use this syntax to find the maximum sales value from our sales table.

SELECT * FROM SalesTable 
WHERE [Sales] = (SELECT MAX([Sales]) FROM SalesTable)
GO

We are using the query to select all the column values from the sales table where the value in the sales column is maximum.

And after executing the above query on the sales table, we will get the following output.

Read SQL Server create stored procedure (15 ways)

How to select min record in SQL Server

In the previous section, we have discussed the selection of a record with maximum value. Now, let’s discuss how we can select a record with a minimum value.

The technique for this task is very much similar to what we have done in the previous topic. We only need to replace the MAX() function with MIN() function. The MIN() function will help to find the minimum value from the selected column. And we can use this function in the WHERE clause to filter out the records where the column value is minimum.

We can use the following syntax to implement the task.

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

The above syntax will help to select a record from a table where the column value is minimum. Now, let’s use this syntax to find the minimum sales value from our sales table.

SELECT * FROM SalesTable 
WHERE [Sales] = (SELECT MIN([Sales]) FROM SalesTable)
GO

After executing the above query on the sales table, we will get the record with a minimum sales value.

Read: Select last 10 records in SQL Server without sorting

How to select first and last record in SQL Server

Till now, we have learned how to get the last record from a SQL Server table. Now, let’s understand how to select the first and last records of a table together.

There can different methods to select the first and last records of a table. In this section, we are going to understand a standard approach to achieve this task. The approach should be as follows.

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

Here is the syntax that we can use to get the first and last records together.

SELECT * FROM ( SELECT TOP 1 * FROM table_name     
                ORDER BY column_name) first
UNION ALL
SELECT * FROM ( SELECT TOP 1 * FROM table_name 
                ORDER BY column_name DESC ) last

In SQL Server, we cannot directly use the UNION operator and ORDER BY clause together. For this implementation, we have to parenthesis. Now, let’s use this syntax to select the first and last records from our sample table.

SELECT * FROM ( SELECT TOP 1 * FROM SampleTable     
                ORDER BY [id]) first
UNION ALL
SELECT * FROM ( SELECT TOP 1 * FROM SampleTable 
                ORDER BY [id] DESC ) last

It is important to use a unique identity column in the ORDER BY clause. In our case, we are using the id column. After implementing the above example, we will get the following output.

Read msg 3609 the transaction ended in the trigger

How to select last 10 records in SQL Server

In SQL Server, we can easily select the last 10 records from a table by using the “SELECT TOP” statement. The TOP clause in SQL Server is used to control the number or percentage of rows from the result. And to select the records from the last, we have to arrange the rows in descending order.

We can use the following script to implement the task and achieve the desired result.

SELECT TOP 10 * FROM table_name
ORDER BY column_name DESC

Usually, we have to implement this task where we have thousands of records in a table. But to simplify things, we are using a sample table that have only 20 records. And we will try to select the last 10 records from it.

Now, let’s use the given syntax to select the last 10 records from our sample table.

USE sqlserverguides
GO

SELECT TOP 10 * FROM MockTable
ORDER BY [id] DESC

After successfully implementing the above example, we will get the last 10 records from the sample table.

How to select max date record in SQL Server

In this topic, we are going to discuss selecting a record having a maximum (recent) date value.

As we already discussed in the previous topic, selecting the maximum value using the MAX() function. Similarly, we can easily use the MAX() function to find the column having the highest date value. Now, the highest date value basically means the most latest date value.

Here is the standard syntax that we can use to select the maximum date record in SQL Server.

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

Now, let’s use the given syntax to select the recent date record from our sample table.

SELECT * FROM SampleTable
WHERE [Date] = (SELECT MAX([Date]) FROM SampleTable)

After executing the above example, the server will return the record with the highest date value in the table.

As there are 2 with the same highest value, the query has returned 2 records.

Read SQL Server trigger after insert with examples

How to select latest date records in SQL Server

While working with the SQL Server table, you might get into a situation where you have a date column with a large number of values. And you want the latest records from that table.

So, in this section, let’s discuss how to select the latest date records from a table in SQL Server.

The implementation of this task is very simple in SQL Server. For this, 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 with come at the bottom.

Here is the syntax that we can use to get the latest date records in SQL Server.

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

Now, let’s use the given syntax to select the last 10 records from our sample table.

Select * From SampleTable 
Order By [Date] Desc;

After executing the above example, the server will return the records in such a way that the records with the latest dates appear at the top.

Read SQL Server convert integer to string + 12 Examples

How to select last inserted record in SQL Server

Directly there is no way in SQL Server to select the last inserted record. Still, in this section, we will try to understand a standard approach that we can use while working with tables in SQL Server.

While creating a table, a best practice is to have one identity column as the primary key in SQL Server. An identity column automatically increases its value when we insert a new record in the table. And, when we create an identity column as the primary key, that column is used to identify each record in the table uniquely.

In SQL Server, we can easily find the last identity value and then use it to fetch the last inserted record.

Let’s understand this implementation with the help of an example, and we will be using the sample table shown at the starting of the article. In the sample table, “id” is a primary identity column.

For demonstration, first, we will insert a new record in the sample table and then find and use the value of last identity value. And by using the last identity value, we will fetch the last inserted record in the table.

Now, we are going to use the following query to insert a new record in the sample table.

INSERT INTO SampleTable values('Dniren', 'Hoolaghan', 'Male', 'South Africa', GETDATE())

Next, we have to use the IDENT_CURRENT() function. This function accepts the table name and returns the last identity value generated for that table. In our case, this function will return 11 as output.

SELECT IDENT_CURRENT('dbo.SampleTable') AS 'LAST ID VALUE'

Now, as we got the last identity value for our table. We can easily use this value as a condition in the WHERE clause to select the last inserted record. For this, we are executing the following query.

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

In the end, we will get the following output.

Read SQL Server Convert Datetime to String

How to get last updated record in SQL Server

The simplest way to get the last updated record is by using the date or DateTime column 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.

The best way to implement this task is by creating a trigger that will automatically update the last modified DateTime value based upon some change. For demonstration, let’s first create a trigger to execute the task for our sample table.

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 dbo.SampleTable
ADD [LastModified] datetime DEFAULT CURRENT_TIMESTAMP 

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

CREATE TRIGGER trg_UpdateLastModified
ON dbo.SampleTable
AFTER UPDATE
AS
UPDATE dbo.SampleTable
SET LastModified = CURRENT_TIMESTAMP
WHERE id IN (SELECT DISTINCT id FROM INSERTED);

In the above query, we have created a trigger that will be executed only when we make some changes in our sample table. And it will automatically update the value of the LastModified column.

Now, let’s modify a record in the sample table, and we are executing the following query.

UPDATE dbo.SampleTable
SET [Country] = 'Australia' WHERE [first_name] = 'Annaliese'

In the above code, we are updating the country name of a record having the first name as “Annaliese“. After the modification is encountered, the trigger will automatically update the value of the “LastModified” column.

In the end, we can use the following query to find the last modified record in the sample table.

SELECT * FROM dbo.SampleTable 
WHERE [LastModified] = (SELECT MAX([LastModified]) FROM dbo.SampleTable)

The above query will return the last modified record as a result. And we will get the following output.

How to select last 1000 rows in SQL Server

In SQL Server, we can easily restrict the number of rows that we want in the result by using the TOP statement. After this, we can arrange the rows in descending order so the rows from last will appear at the top.

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

SELECT TOP 1000 * FROM table_name 
ORDER BY identity_column DESC

Let’s implement the above approach on our sample table to fetch the last 1000 from it.

SELECT TOP 1000 * FROM dbo.SampleTable 
ORDER BY [id] DESC

In our case, the “id” is a unique identity column that is used to identify each row uniquely.

Read SQL Server drop table if exists

How to get last week data in SQL Server

In SQL Server, we do not have any direct function which 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 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

The implementation of the query to get the last 6 months’ data is almost similar to what we have explained in the previous section.

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 will subtract 6 months from the current date and returns the result. The result from DATEADD() will be 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 6 months as a result.

You may also like the following SQL server tutorials:

• PostgreSQL vs SQL Server: Detailed Comparison
• SQL Server stored procedure if else
• IDENTITY_INSERT in SQL Server
• SQL Server stored procedure parameters

So, in this tutorial, we have discussed How to select records from a SQL Server Table, Different scenarios on selecting records in SQL Server, and we have covered the following topic:

• How to select latest record in SQL Server
• How to select last record in SQL Server
• How to select max record in SQL Server
• How to select min record in SQL Server
• How to select latest date records in SQL Server
• How to select last inserted record in SQL Server
• How to get last updated record in SQL Server
• How to select last 10 records in SQL Server
• How to select first and last record in SQL Server
• How to select max date record in SQL Server
• How to select last 1000 rows in SQL Server
• How to get last week data in SQL Server
• How to get last 6 months data in SQL Server