SQL – SELECT LAST
SEQUEL widely known as SQL (Structured Query Language), is the most popular standard language to work on databases. It is a domain-specific language that is mostly used to perform tons of operations which include creating a database, storing data in the form of tables, modifying, extract and lot more. There are different versions of SQL like MYSQL, PostgreSQL, Oracle, SQL lite, etc.
SQL became the norm of American National Standards Institute (ANSI) in the year 1986 and the next year in 1987 it became the norm of the International Organization for Standardization (ISO). Today is the world of the Internet and technology. We are surrounded by tons of data. So to store this data securely and to manage them we need a proper database and to manage this database we need a language which is SQL.
In this article, we are going to look at how to get the last entry of any record in a Table. We are going to discuss four different ways to extract the last entry of any given table in a database.
Sample Input: Consider a Table “Student Information” which contains the data about the students enrolled in “GeekforGeeks DSA course“.
1. Creating a Database
CREATE DATABASE database_name;
2. Creating a Table
CREATE TABLE Table_name( col_1 TYPE col_1_constraint, col_2 TYPE col_2 constraint ..... ); col: Column name TYPE: Data type whether an integer, variable character, etc. col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc.
3. Inserting into a Table
INSERT INTO Table_name VALUES(val_1, val_2, val_3, ..........); val: Values in particular column.
4. View The Table
SELECT * FROM Table_name
Method 1: Using MS Access
We can use the command FIRST() to extract the first entry of a particular column and LAST() to extract the last entry of a particular column in a Table. For more information visit First() and Last()Function in MS Access.
Basic Syntax :
For example, say we want to extract the last student name from the table “Student Information”
SELECT LAST(Student_name) AS Stud_Name FROM StudentInformation;
As we can see, the last student name “Hina” is extracted using the above query. But it is to be noted that SELECT LAST or LAST(expression) is only supported in MS Access. This statement doesn’t support MYSQL, Oracle, PostgreSQL, etc. There are alternate ways as discussed below to perform the above operation in other versions of SQL like MYSQL, Oracle, PostgreSQL, etc.
Method 2: By sorting the data
We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table. And then we can select the entry which we want to retrieve.
MYSQL syntax :
SELECT col_name(s) FROM Table_Name ORDER BY appr_col_name DESC LIMIT 1; col_name(s): The name of the column(s). appr_col_name: Appropriate column name to perform ORDER BY.
Oracle syntax :
SELECT col_name(s) FROM Table_Name ORDER BY appr_col_name DESC WHERE ROWNUM <= 1; col_name(s): The name of the column(s). appr_col_name: Appropriate column name to perform ORDER BY.
It is important to note that in order to perform sorting, the column needs to be chosen properly. For example, if we choose “ORDER BY Student_name DESC” then it will alphabetically sort the table on the basis of names. So, the row containing “Vishal” will come at the top, but the row having “Vishal” as entry is not the last row of the table. Also, we can’t use the column “Age” to perform ORDER BY as shown below:
Hence, it is mandatory to use the column ID or any column which is unique and sequentially increasing with every record in the table.
Method 3: By using Subquery and AGGREGATE MAX()
Subquery is nothing but a query inside another query that maintains a parent-child relationship. The inner query will execute first followed by the outer query. Here, in this method, the basic idea is to get the maximum ID using aggregate function MAX and then select the student name associated with that maximum ID. In this way, we can extract the last student’s name from the table.
SELECT col_name(s) FROM Table_Name WHERE appr_col_name = ( SELECT MAX(appr_col_name) FROM Table_Name ); col_name(s): The name of the column(s). appr_col_name: Appropriate column name. For example ID.
Method 4: By comparative or relative approach
In this method also, we are going to use a subquery. The basic idea is to filter out the rows and to check that no such row exists having higher ID values than the row we are going to extract. This will help in getting the row with maximum ID and hence we can retrieve the last information. It is a complex query and is an iterative approach in which we are going to use the NOT EXISTS statement. This method will take more time to execute if there are more records in the table.
SELECT col_name(s) FROM Table_Name t1 WHERE NOT EXISTS( SELECT * FROM Table_Name t2 WHERE t2.appr_col_name > t1.appr_col_name ); col_name(s): The name of the column(s). appr_col_name: Appropriate column name. For example ID.