CODEWITHSUNDEEP
sql-servert-sql
Randy Minder
Randy Minder

Reputation: 48432

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

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.

Upvotes: 3

Views: 3732

Answers (4)

shannon
shannon

Reputation: 8784

The PARTITION/OVER functions are look-ahead column functions. They aren't row functions - by that, I mean, they don't effect an entire row, number of rows returned, etc. An OVER aggregate can depend on values in other rows, but the tangible result is only to calculate a single column in the current row.

You may have seen something similar to what you are trying to do via an OVER ROW_NUMBER ranking function. Multiple rows are still returned, but only one of them has a ROW_NUMBER of 1. The rest are filtered in an encapsulating WHERE or JOIN predicate.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

FIRST_VALUE() will still return a row for every record that meets tour WHERE clause. TOP 1 should work:

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

or MIN:

Select PH.SubId
From dbo.PanelHistory PH
Where 
    PH.Scribe2Time <> (Select MIN(ReadTimeLocal)
                       From dbo.PanelWorkflow 
                       Where ProcessNumber = 2690 
                           And dbo.PanelWorkflow.SubId = PH.SubId)

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

This isn't an answer. It's just an extended comment generated by the following conclusion:

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

Min can't be used instead of FIRST_VALUE.

Example:

SET NOCOUNT ON;
DECLARE @MyTable TABLE(ID INT, TranDate DATETIME)
INSERT  @MyTable VALUES (1, '2012-02-02'), (2, '2011-01-01'), (3, '2013-03-03')

SELECT  MIN(ID) AS MIN_ID FROM @MyTable
SELECT  ID, MIN(ID) OVER(ORDER BY TranDate) AS MIN_ID_ORDER_BY FROM @MyTable;
SELECT  ID, FIRST_VALUE(ID) OVER(ORDER BY TranDate) AS FIRST_VALUE_ID_ORDER_BY FROM @MyTable;

Results:

MIN_ID
-----------
1

ID          MIN_ID_ORDER_BY
----------- ---------------
2           2
1           1
3           1

ID          FIRST_VALUE_ID_ORDER_BY
----------- -----------------------
2           2
1           2
3           2

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453368

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.

Upvotes: 3

Related Questions