SQL SELECT LAST

SQL SELECT LAST Statement

The SQL SELECT LAST statement is used to retrieve the last row from a query result. This command is essential for fetching the most recent or highest value in a column, often used for sorting and retrieving the last record based on specific criteria. Note that different SQL databases may use different syntax for this operation (e.g., ORDER BY ... DESC LIMIT 1 in MySQL).

---

Syntax

-- For SQL Server
SELECT TOP 1 column1, column2, ...
FROM table_name
ORDER BY column_name DESC;

-- For MySQL and PostgreSQL
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name DESC
LIMIT 1;

-- For Oracle
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name DESC
FETCH FIRST 1 ROW ONLY;

• SELECT TOP 1: This is the SQL keyword used in SQL Server to specify the top 1 row.
• column1, column2, ...: These specify the names of the columns to retrieve.
• FROM: This is the SQL keyword used to specify the table.
• table_name: This specifies the name of the table from which to retrieve the row.
• ORDER BY column_name DESC: This specifies the column by which to sort the rows in descending order to get the last row.
• LIMIT 1: This specifies the limit of one row to be returned (MySQL and PostgreSQL).
• FETCH FIRST 1 ROW ONLY: This specifies the limit of one row to be returned (Oracle).

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the SELECT LAST statement to retrieve the last row.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table.

Step 4: Using the SELECT LAST Statement

This step involves retrieving the last row from the employees table using the SELECT LAST statement (syntax for SQL Server).

SELECT TOP 1 * FROM employees
ORDER BY hire_date DESC;

This command will return the last row from the employees table ordered by the hire_date column in descending order.

For MySQL and PostgreSQL, the equivalent command is:

SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 1;

For Oracle, the equivalent command is:

SELECT * FROM employees
ORDER BY hire_date DESC
FETCH FIRST 1 ROW ONLY;

These commands will return the last row from the employees table ordered by the hire_date column in their respective SQL database systems.