T-SQL First_Value() - How can it return more than a single value?

Question

I have the following query:

Select PH.SubId
From dbo.PanelHistory PH
Where 
    PH.Scribe2Time <> (Select FIRST_VALUE(ReadTimeLocal) OVER (Order By ReadTimeLocal) From dbo.PanelWorkflow Where ProcessNumber = 2690 And dbo.PanelWorkflow.SubId = PH.SubId)

I'm getting an error (512) that says: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can the subquery return more than a single value? There can only be one first value. I must be overlooking something with this query.

By the way, I realize I could easily use Min() instead of First_Value, but I wanted to experiment with some of these Windowing functions.

Answer 1

How many rows do you see?

SELECT FIRST_VALUE(name) OVER (ORDER BY create_date) AS RN
FROM   sys.objects 

Even though there is only one distinct first value it still returns it for every row in the query.

So if the sub query itself matches multiple rows you will get this error. You could get rid of it with DISTINCT or TOP 1.

Probably not very efficient but you say this is just for experimental purposes.