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_ID | USER_NAME | DEPARTMENT | LOGIN_TIME | LOGIN_EXIST |
1 | sbrandt0 | Product Management | 18-02-2022 | 18-02-2022 |
2 | dmilesop1 | Product Management | 25-06-2022 | 25-06-2022 |
3 | jyeude2 | Legal | 01-01-2022 | 01-01-2022 |
4 | ndraye3 | Services | 08-06-2022 | 08-06-2022 |
6 | hbuntine4 | Training | 09-04-2022 | 09-04-2022 |
6 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
7 | fevans6 | Legal | 21-08-2022 | 21-08-2022 |
8 | escopham7 | Services | 08-03-2022 | 08-03-2022 |
9 | kspary8 | Research and Development | 02-01-2022 | 02-01-2022 |
10 | rcarver9 | Research and Development | 13-11-2022 | 13-11-2022 |
11 | dmilesop1 | Product Management | 25-07-2022 | 25-07-2022 |
12 | jyeude2 | Legal | 01-01-2022 | 01-01-2022 |
13 | ndraye3 | Services | 08-06-2022 | 08-06-2022 |
6 | hbuntine4 | Training | 09-04-2022 | 09-04-2022 |
15 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
16 | msowersby0 | Research and Development | 03-11-2022 | 03-11-2022 |
17 | krickman6 | Sales | 16-09-2022 | 16-09-2022 |
6 | hbuntine4 | Training | 14-08-2022 | 14-08-2022 |
6 | hbuntine4 | Training | 18-09-2022 | 18-09-2022 |
20 | bkitchenside4 | Services | 15-03-2022 | 15-03-2022 |
21 | cwanne5 | Support | 02-03-2022 | 02-03-2022 |
22 | krickman6 | Sales | 18-10-2022 | 18-10-2022 |
23 | krickman6 | Sales | 24-04-2022 | 24-04-2022 |
24 | pdrinkhall8 | Research and Development | 25-08-2022 | 25-08-2022 |
25 | smaltby9 | Product Management | 02-02-2022 | 02-02-2022 |

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_ID | USER_NAME | DEPARTMENT | LOGIN_TIME | LOGIN_EXIST |
6 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
7 | fevans6 | Legal | 21-08-2022 | 21-08-2022 |
8 | escopham7 | Services | 08-03-2022 | 08-03-2022 |
9 | kspary8 | Research and Development | 02-01-2022 | 02-01-2022 |
10 | rcarver9 | Research and Development | 13-11-2022 | 13-11-2022 |
11 | dmilesop1 | Product Management | 25-07-2022 | 25-07-2022 |
12 | jyeude2 | Legal | 01-01-2022 | 01-01-2022 |
13 | ndraye3 | Services | 08-06-2022 | 08-06-2022 |
14 | hbuntine4 | Training | 09-04-2022 | 09-04-2022 |
15 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
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_ID | USER_NAME | DEPARTMENT | LOGIN_TIME | LOGIN_EXIST |
1 | sbrandt0 | Product Management | 18-02-2022 | 18-02-2022 |
2 | dmilesop1 | Product Management | 25-06-2022 | 25-06-2022 |
3 | jyeude2 | Legal | 01-01-2022 | 01-01-2022 |
4 | ndraye3 | Services | 08-06-2022 | 08-06-2022 |
5 | hbuntine4 | Training | 09-04-2022 | 09-04-2022 |
6 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
7 | fevans6 | Legal | 21-08-2022 | 21-08-2022 |
8 | escopham7 | Services | 08-03-2022 | 08-03-2022 |
9 | kspary8 | Research and Development | 02-01-2022 | 02-01-2022 |
10 | rcarver9 | Research and Development | 13-11-2022 | 13-11-2022 |
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_ID | USER_NAME | DEPARTMENT | LOGIN_TIME | LOGIN_EXIST |
16 | msowersby0 | Research and Development | 03-11-2022 | 03-11-2022 |
17 | krickman6 | Sales | 16-09-2022 | 16-09-2022 |
20 | bkitchenside4 | Services | 15-03-2022 | 15-03-2022 |
21 | cwanne5 | Support | 02-03-2022 | 02-03-2022 |
22 | krickman6 | Sales | 18-10-2022 | 18-10-2022 |
23 | krickman6 | Sales | 24-04-2022 | 24-04-2022 |
24 | pdrinkhall8 | Research and Development | 25-08-2022 | 25-08-2022 |
25 | smaltby9 | Product Management | 02-02-2022 | 02-02-2022 |
You may also like to read the following SQL Server tutorials.
- How to drop table if exists in SQL Server
- Find Stored Procedure by Name in SQL Server
- INSERT INTO SELECT Statement in SQL Server
- How to create foreign key in SQL Server
- How to add column in SQL Server
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.
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.