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:

Row Department Employee_Name Salary
1 HR John Smith 32500
2 HR Jane Doe 18350
3 HR Mike Smith 42730
4 IT Ben Dover 50370
5 IT Anita Mann 90450
6 IT Joe King 30920
7 Operations Justin Case 18350
8 Operations Kandi Barr 43290
9 Operations Hugo First 80500

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:

	First(Employee_Name) as Employee_Name, 
	First(Salary) as Salary 
		Salary DESC
	) AS [Ordered Salaries]

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):

	RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Ranking 

This query would return:

Department Employee_Name Salary Ranking
HR John Smith 32500 2
HR Jane Doe 18350 3
HR Mike Smith 42730 1
IT Ben Dover 50370 2
IT Anita Mann 90450 1
IT Joe King 30920 3
Operations Justin Case 18350 3
Operations Kandi Barr 43290 2
Operations Hugo First 80500 1

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:

	Department, Name, Salary 
		RANK() OVER(PARTITION BY Department ORDER BY Salary Desc) AS Ranking 
	) AS [Ranked Salaries]
	Ranking = 1

And the final result:

Department Employee_Name Salary
HR Mike Smith 42730
IT Anita Mann 90450
Operations Hugo First 80500

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.

About GeekLad

Tagged . Bookmark the permalink.