SQL SELECT FIRST and LAST Functions

SQL SELECT FIRST and LAST Functions

Introduction

Structured Query Language (SQL) provides various functions to retrieve data from a database efficiently. Among these, the FIRST() and LAST() functions are commonly used in Microsoft Access to fetch the first and last values from a specified column. However, these functions are not supported in popular databases like MySQL and Oracle, which offer alternative methods such as LIMIT and ROWNUM.

Complete Advance AI topics:- CLICK HERE
Complete Python Course with Advance topics:-Click here

SQL SELECT FIRST

The FIRST() function in SQL is used to return the first value of a selected column. This function is exclusively supported by Microsoft Access.

Syntax:

SELECT FIRST(column_name) FROM table_name;

Example:

Consider the following CUSTOMER_INFO table:

If you want to retrieve the first value of the CUSTOMER_NAME column, the SQL query will be:

SELECT FIRST(CUSTOMER_NAME) AS first_customer FROM CUSTOMER_INFO;

Output:

ARJUN MEHTA

Note: The SELECT FIRST statement is only supported by MS Access and does not work in databases like MySQL and Oracle.

SQL SELECT LAST

The LAST() function is used to retrieve the last value of a selected column. Like FIRST(), it is only available in Microsoft Access. In other databases, alternative approaches such as ORDER BY and LIMIT (for MySQL) or ROWNUM (for Oracle) can be used.

Syntax:

SELECT LAST(column_name) FROM table_name;

Example:

Consider the following STUDENT_RECORDS table:

To fetch the last student’s name, the SQL query will be:

SELECT LAST(STUDENT_NAME) AS last_student FROM STUDENT_RECORDS;

Output:

SONIA GUPTA

Alternative Approaches for Other Databases

MySQL:

In MySQL, the LIMIT clause can be used to retrieve the last record:

SELECT STUDENT_NAME FROM STUDENT_RECORDS ORDER BY STUDENT_NAME DESC LIMIT 1;

Output:

SONIA GUPTA

Oracle:

Oracle uses the ROWNUM keyword to achieve similar functionality:

SELECT STUDENT_NAME FROM STUDENT_RECORDS ORDER BY STUDENT_NAME DESC WHERE ROWNUM <= 1;

Output:

SONIA GUPTA

Download New Real Time Projects :-Click here

Conclusion

The FIRST() and LAST() functions are useful for quickly retrieving the first and last values from a column, but they are limited to MS Access. For other databases like MySQL and Oracle, developers must rely on ORDER BY with LIMIT or ROWNUM to achieve similar results.

Stay updated with more SQL tutorials and insights on UpdateGadh.

SQL SELECT FIRST and LAST Functions
sql select first and last record of each group
how to get first and last record from a table in sql server
select last record in sql without order by
select last 10 rows sql
sql select last row with condition
sql select first and last functions record in group
select first and last row sql oracle
sql select most recent record for each id by date
sql select first and last functions w3schools
sql select first and last functions oracle
sql select first and last functions examples