SQL SELECT LAST

The LAST() function in Structured Query Language shows the last value from the specified column of the table.

Note: This SQL function is only supported in Microsoft Access database. Oracle supports ORDER BY and ROWNUM keywords, and MySQL supports the LIMIT keyword for selecting the last record.

Syntax of LAST() Function

SELECT LAST (Field_Name) FROM Table_Name ;  

In the above syntax, the LAST keyword denotes the last row to be shown from the table in the output, and the Field_Name denotes the column whose value we want to show.

Example of the LAST function in SQL

Example 1:

Firstly, we have to create a table and insert the data into the table in SQL.

The following SQL statement creates the Student_Details table with Student_ID as the primary key:

CREATE TABLE Student_Details  

(  

Student_ID INT NOT NULL,   

Student_Name varchar(100),  

Student_Course varchar(50),  

Student_Age INT,   

Student_Marks INT  

);  

The following SQL queries insert the record of students into the above table using INSERT INTO statement:

INSERT INTO Student_Details VALUES (101, Anuj, B.tech, 20, 88);  

INSERT INTO Student_Details VALUES (102, Raman, MCA, 24, 98);  

INSERT INTO Student_Details VALUES (104, Shyam, BBA, 19, 92);  

INSERT INTO Student_Details VALUES (107, Vikash, B.tech, 20, 78);  

INSERT INTO Student_Details VALUES (111, Monu, MBA, 21, 65);  

INSERT INTO Student_Details VALUES (114, Jones, B.tech, 18, 93);  

INSERT INTO Student_Details VALUES (121, Parul, BCA, 20, 97);  

INSERT INTO Student_Details VALUES (123, Divya, B.tech, 21, 89);  

INSERT INTO Student_Details VALUES (128, Hemant, MBA, 23, 90);  

INSERT INTO Student_Details VALUES (130, Nidhi, BBA, 20, 88);  

INSERT INTO Student_Details VALUES (132, Priya, MBA, 22, 99);  

INSERT INTO Student_Details VALUES (138, Mohit, MCA, 21, 92);

Let’s see the record of the above table using the following SELECT statement:

SELECT * FROM Student_Details;  

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	78
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	89
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	99
138	Mohit	MCA	21	92

The following query shows the last Student_Name from the above table in the output:

SELECT LAST (Student_Name) AS Last_Student FROM Student_Details;  

Output:

Syntax of LIMIT Clause in MySQL

SELECT column_Name FROM Table_Name ORDER BY Column_Name DESC LIMIT 1;  

In this MySQL syntax, we have to specify the value 1 just after the LIMIT keyword for indicating the single row/record.

Example of LIMIT Clause in MySQL

Let’s take the following Employee table to explain how to use the LIMIT clause in MySQL for accessing the last record:

Employee_Id	Emp_Name	Emp_City	Emp_Salary	Emp_Bonus
101	Anuj	Ghaziabad	35000	2000
102	Tushar	Lucknow	29000	3000
103	Vivek	Kolkata	35000	2500
104	Shivam	Goa	22000	3000

The following MySQL query shows the last value of the Emp_City column from the above Employee table:

SELECT Emp_City FROM Employee ORDER BY Emp_City DESC LIMIT 1;  

Output:

Goa

ROWNUM keyword in Oracle

The syntax for accessing the last record from the Oracle database is given below:

SELECT Column_Name FROM Table_Name ORDER BY Column_Name DESC WHERE ROWNUM <=1;  

In this Oracle syntax, we have to specify the ROWNUM keyword, which is less than and equal to 1. In Oracle, the ROWNUM keyword is used in the WHERE clause for retrieving the last record from the table.

Example of ROWNUM Clause in Oracle

Let’s take the following Cars table to explain how to use the ROWNUM keyword in MySQL:

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	900000
9258	Audi	2	1100000
8233	Venue	6	900000
6214	Nexon	7	1000000

The following MySQL query shows the last name of the car from the Car_Name column of the Cars table:

SELECT Car_Name FROM Cars ORDER BY Car_Name DESC WHERE ROWNUM <=1;  

Output:

Nexon