Is there a way to do FIRST() in SQL Server?

Question

From my old Access days, there was a First() function that allowed you to get the first row as an aggregate function. Is there any equivalent in SQL Server?

SELECT
  c.ID
, p.ID
, FIRST(p.ProductName)
, SUM(fee.Amount)
from Fee as f
INNER JOIN Product as p
ON p.ID = f.ProductID
INNER JOIN Customer as c
ON c.ID = p.CustomerID
GROUP BY c.ID, p.ID

Edit: I just wanted a value from any row, since they are all going to be the same. I was trying to be nice to the database and let it just give me the first one that it finds :)

Answer 1

Well, it depends.

You mean "any single row"? Then you can use MIN or MAX, it should work with most data types.

However, if you mean "the first row you can find", then the answer is no.

That's akin to telling the database engine that "I want you to give me a particular row, that fits these criteria, and one of the criteria is that you can give me any row you want".

The reason for this is that unless you order the rows, there's no concept of first, and you can't order the rows in any meaningful way that would work with a group this way.

Answer 2

You could try:

SELECT  c.ID, p.ID, 
(SELECT TOP 1 ProductName FROM Product ORDER BY ID) AS ProductName, 
SUM(fee.Amount)
FROM Fee as f
INNER JOIN   Product as pON p.ID = f.ProductID
INNER JOIN Customer as cON c.ID = p.CustomerIDGROUP BY c.ID, p.ID

This one gets the first product directly from the Product Table as a sub-query. The ORDER BY ID in the sub query should get you the first ProductName in the Product table.