Select last 10 records in SQL Server without sorting

by
Select last 10 records in SQL Server without sorting

In this SQL Server tutorial, we will learn how to fetch or select the last 10 records from a table in SQL Server without sorting.

Before we see how to fetch the last 10 records in SQL Server without sorting. Here is the image of the EMPLOYEE_ACCESS table which is used in the SQL Server.

ACCESS_IDUSER_NAMEDEPARTMENTLOGIN_TIMELOGIN_EXIST
1sbrandt0Product Management18-02-202218-02-2022
2dmilesop1Product Management25-06-202225-06-2022
3jyeude2Legal01-01-202201-01-2022
4ndraye3Services08-06-202208-06-2022
6hbuntine4Training09-04-202209-04-2022
6kswinden5Human Resources06-08-202206-08-2022
7fevans6Legal21-08-202221-08-2022
8escopham7Services08-03-202208-03-2022
9kspary8Research and Development02-01-202202-01-2022
10rcarver9Research and Development13-11-202213-11-2022
11dmilesop1Product Management25-07-202225-07-2022
12jyeude2Legal01-01-202201-01-2022
13ndraye3Services08-06-202208-06-2022
6hbuntine4Training09-04-202209-04-2022
15kswinden5Human Resources06-08-202206-08-2022
16msowersby0Research and Development03-11-202203-11-2022
17krickman6Sales16-09-202216-09-2022
6hbuntine4Training14-08-202214-08-2022
6hbuntine4Training18-09-202218-09-2022
20bkitchenside4Services15-03-202215-03-2022
21cwanne5Support02-03-202202-03-2022
22krickman6Sales18-10-202218-10-2022
23krickman6Sales24-04-202224-04-2022
24pdrinkhall8Research and Development25-08-202225-08-2022
25smaltby9Product Management02-02-202202-02-2022
EMPLOYEE_ACCESS table
Select last 10 records in SQL Server without sorting

Select the last 10 records in SQL Server without Sorting

To bring the last 10 records from a table in SQL Server, we will have to use the SQL Server TOP keyword with the SUBQUERY and ORDER BY clause. Let’s see an example of how to fetch the last 10 records in the SQL Server ORDER BY clause using sorting.

EXAMPLE_1:

SELECT * FROM
(SELECT TOP 10 * FROM EMPLOYEE_ACCESS
ORDER BY ACCESS_ID DESC)VAR1
ORDER BY ACCESS_ID ASC;

A SUBQUERY returns all records and the ACCESS_ID column is arranged in ascending order based on the OUTER SELECT statement.

All records will be returned in ascending order. In the INNER SELECT statement, we retrieve the first 10 records for all columns from the EMPLOYEE_ACCESS table based on ORDER BY.

By ORDER BY, we arrange ACCESS_ID records descendingly. This means that SQL Server will retrieve the last ten records from the table with sorting.

ACCESS_IDUSER_NAMEDEPARTMENTLOGIN_TIMELOGIN_EXIST
6kswinden5Human Resources06-08-202206-08-2022
7fevans6Legal21-08-202221-08-2022
8escopham7Services08-03-202208-03-2022
9kspary8Research and Development02-01-202202-01-2022
10rcarver9Research and Development13-11-202213-11-2022
11dmilesop1Product Management25-07-202225-07-2022
12jyeude2Legal01-01-202201-01-2022
13ndraye3Services08-06-202208-06-2022
14hbuntine4Training09-04-202209-04-2022
15kswinden5Human Resources06-08-202206-08-2022
Example of how to select the last 10 records in SQL Server with sorting

Now, we will see what happens if we try to fetch the last 10 records of the table in a SQL Server but without sorting. So, we will use now the SQL Server TOP keyword but no ORDER BY clause on the table.

EXAMPLE_2:

SELECT * FROM
(SELECT TOP 10 * FROM EMPLOYEE_ACCESS)VAR1;

In the OUTER SELECT statement, we retrieve all records with the SUBQUERY table named VAR1. Whereas in the INNER SELECT statement, we retrieve the first 10 records of all columns from the EMPLOYEE_ACCESS table.

ACCESS_IDUSER_NAMEDEPARTMENTLOGIN_TIMELOGIN_EXIST
1sbrandt0Product Management18-02-202218-02-2022
2dmilesop1Product Management25-06-202225-06-2022
3jyeude2Legal01-01-202201-01-2022
4ndraye3Services08-06-202208-06-2022
5hbuntine4Training09-04-202209-04-2022
6kswinden5Human Resources06-08-202206-08-2022
7fevans6Legal21-08-202221-08-2022
8escopham7Services08-03-202208-03-2022
9kspary8Research and Development02-01-202202-01-2022
10rcarver9Research and Development13-11-202213-11-2022
Example_2 of How to fetch the last 10 records in SQL Server without sorting

We hope you understand the example of how to bring the last 10 records from a table in SQL Server without sorting.

EXAMPLE_3:

SELECT * FROM EMPLOYEE_ACCESS
WHERE 
ACCESS_ID > (SELECT COUNT(*) FROM EMPLOYEE_ACCESS)-10;

The SELECT statement retrieves all records from the EMPLOYEE_ACCESS table by the WHERE condition. In the WHERE condition, we have used the GREATER THAN operator between the ACCESS_ID column and another INNER SELECT statement.

In the INNER SELECT statement, we counted all records from the EMPLOYEE_ACCESS table and then deducted them by 10. After the deduction part, we will check the INNER SELECT query value is less than the ACCESS_ID column value. So, this method will bring the last 10 records from the table into SQL Server.

ACCESS_IDUSER_NAMEDEPARTMENTLOGIN_TIMELOGIN_EXIST
16msowersby0Research and Development03-11-202203-11-2022
17krickman6Sales16-09-202216-09-2022
20bkitchenside4Services15-03-202215-03-2022
21cwanne5Support02-03-202202-03-2022
22krickman6Sales18-10-202218-10-2022
23krickman6Sales24-04-202224-04-2022
24pdrinkhall8Research and Development25-08-202225-08-2022
25smaltby9Product Management02-02-202202-02-2022
Example_3 of How to fetch the last 10 records in SQL Server without sorting

You may also like to read the following SQL Server tutorials.

Conclusion

In this SQL Server tutorial, we understood how to fetch or select the last 10 records from a SQL Server table by using the SQL Server ORDER BY clause and without using SQL Server ORDER BY clause.