SQL First Function Equivalent in SQL Server and Oracle

Microsoft Access is nice for quick and dirty manipulation of data and merging data sets. It also has a lot of nice functions you won’t find in other versions of SQL. One of these functions is the First() function. Although not available in Microsoft SQL Server or Oracle, it can be emulated by other means.

The trick is to use the RANK() and OVER clauses. When combined together, RANK() and OVER can perform aggregation in a query without having to use the GROUP BY clause. This allows you to obtain aggregation information for individual rows in a result. Unfortunately, this does not work in MySQL, but it does work in both Oracle and T-SQL (transact SQL, which is what MS SQL Server uses).

For example, let’s say you have the following table called employees:

You want to select only those individuals that have the highest salary, by department. In MS Access, you could do this with the First() function:

SELECT 
	Department, 
	First(Employee_Name) as Employee_Name, 
	First(Salary) as Salary 
FROM
	(SELECT
		* 
	FROM 
		employees 
	ORDER BY 
		Department, 
		Salary DESC
	) AS [Ordered Salaries]
GROUP BY 
	Department

This will create an inner query that sorts by department and then descending salary. The outer query groups by department and selects the first salary and name. Because the inner query is sorted, the First() function returns the employee with the highest salary for each department.

You can simulate the First() function in SQL Server and Oracle, with RANK() OVER. Here is what the inner query would look like in SQL Server (T-SQL):

SELECT 
	employees.Department, 
	employees.Employee_Name, 
	employees.Salary, 
	RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Ranking 
FROM 
	employees

This query would return:

Notice how the RANK() OVER(PARTITION BY) clause provides aggregated information for rows in the result, without having to use the GROUP BY clause. This can be a very powerful tool, especially considering that other aggregation functions such as MIN(), MAX(), SUM(), etc. can be used with OVER(PARTITION BY).

You can probably already see where this is going. The PARTITION BY Department portion of the query aggregates the table by department, and the results are then ordered descending by salary.

To get the final result we desire, all we need to do is select the rows of the inner query where the Ranking is 1. Here’s what the final query looks like:

SELECT 
	Department, Name, Salary 
FROM
	(SELECT 
		employees.Department, 
		employees.Name, 
		employees.Salary, 
		RANK() OVER(PARTITION BY Department ORDER BY Salary Desc) AS Ranking 
	FROM 
		employees
	) AS [Ranked Salaries]
WHERE 
	Ranking = 1
GROUP BY 
	Department

And the final result:

Although the First() function may be a bit more intuitive in Access, emulating it with RANK() OVER(PARTITION BY) works just as well. The syntax for Oracle PL/SQL is exactly the same:

RANK() OVER(PARTITION BY Some_Column ORDER BY Some_Other_Column)

You just need to put the column(s) you want to group after the PARTITION BY and then sort the data accordingly. After you use it a few times, you’ll get the hang of it. You can also use this method to obtain the top results for a category. You would simply just select records with a ranking greater than or equal to the top number of results you want to see. Perhaps that will be another tutorial for another time.