SQL Server Select Newest Record

Getting the latest record from the table is an easy and simple method, and you do not need much knowledge of SQL to do so. You may want to retrieve the most recent record from the table with every column, or you may want to retrieve just particular columns.

This tutorial will discuss simple and easy ways to select the newest record in SQL Server.

Select Newest Record in SQL

To select the newest record in SQL, use the syntax below. Before that, ensure you have a proper table in SQL to proceed to the next steps.

select * from EmpDetails

Using this syntax, you can see all the column details in the table.

So if you want to see the particular detail in HospitalInfo table, use the below syntax.

Select Branchname From HospitalInfo
Order By Hospitalname 
Query to get latest record in SQL

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

select top 2 Hospitalname, Branchname from HospitalInfo
order by Hospitalid
SQL Server latest record in table

Select Maximum record in SQL

Sometimes, the user faces a situation like needing 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 HospitalInfo where I want to take a maximum record for the column Totalstaffs and see the output.

SELECT Totalstaffs FROM HospitalInfo

WHERE Totalstaffs = (SELECT MAX(Totalstaffs) FROM HospitalInfo)
How to get last updated record in sql server

Select Minimum Record in SQL Server

In the previous step, we saw how to select minimum 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 HospitalInfo. And the output is below.

SELECT * FROM HospitalInfo
WHERE [Totalstaffs] = (SELECT MIN([Totalstaffs]) FROM HospitalInfo)
GO 
Newest record in SQL

Use Row_number()

We can use row_number() to get the record per user requirements.

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY Role DESC) AS rn
  FROM EmpDetails
) AS subquery
WHERE rn = 1;
Latest record in SQL Server

By doing this, you can select Role by descending by using row_number.

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 simultaneously choose a table’s first and last records.

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 students
                ORDER BY [student_id]) first
UNION ALL  
SELECT * FROM  ( SELECT TOP 1 * FROM  students
               ORDER BY [student_id] Desc) last
First and Last record in SQL table

Select the Last 3 records in the SQL Server

In SQL Server, we can easily select the last 03 records from a table by using the “SELECT TOP” statement.

SELECT TOP 3 * FROM table_name
ORDER BY column_name DESC

Here, I have a student table; use the syntax below to get the output.


SELECT TOP 3 * FROM students
ORDER BY [student_id] Desc
Last recent records in SQL Server

Fetch the Last Inserted Record in SQL

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.

INSERT INTO students values(7, 'Peter', 'william', 'Accounts', 22)

SELECT IDENT_CURRENT('dbo.students') AS 'LAST ID VALUE'
                
Last value in SQL Table

If you want to check the current record added in the table, use the below syntax.

SELECT * FROM dbo.students WHERE [student_id] = (SELECT IDENT_CURRENT('dbo.students'))
Current record in SQL Table

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.

Select * From smartproduct
Order By [order_date] Desc;
Newest record in SQL Server

Syntax to get the Last six months Data

We can use the DATEADD() function to fetch the recent 6 months data by comparing dates in the WHERE Clause.

Here is the syntax 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.

Conclusion

So, this tutorial covered enough information about selecting the newest record in SQL Server. Try the different scenarios for your table and see the changes.

Read Also,