CODEWITHSUNDEEP
sql-server
Even Mien
Even Mien

Reputation: 45898

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

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

Upvotes: 4

Views: 275

Answers (4)

jrcs3
jrcs3

Reputation: 2780

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.

Upvotes: 4

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391416

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.

Upvotes: 5

KM.
KM.

Reputation: 103607

not that I know about, just use MIN()

Upvotes: 1

pgb
pgb

Reputation: 25001

You can do SELECT TOP 1 * FROM ... to get only the first row.

Upvotes: 2

Related Questions