CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Pomster
Pomster

Reputation: 15197

Select the Id of the latest entry with a value of ***

I need to get the Id of an entry containing a value.

This is what I have used:

SET @ValuationId = (SELECT ValuationId FROM dbo.Valuation WHERE ValuationPropertyId = @ValuationPropertyId)

The problem with the above is that it selects multiple values, what can I use to get it to select the latest value?

Here is the error message I was given:

Msg 512, Level 16, State 1, Procedure GetLatestValuationOfPropIdThenCallUpdateComparibleSalesInfo, Line 15 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 2

Views: 34

Answers (1)

John Woo
John Woo

Reputation: 263743

use MAX()

SET @ValuationId = (SELECT MAX(ValuationId) 
                    FROM   dbo.Valuation
                    WHERE  ValuationPropertyId = @ValuationPropertyId)

or

SET @ValuationId = (SELECT TOP 1 ValuationId
                    FROM   dbo.Valuation
                    WHERE  ValuationPropertyId = @ValuationPropertyId
                    ORDER  BY ValuationId DESC)

Upvotes: 2

Related Questions