How to create a function that returns the first value of sorted list in SQL?

I'm writing a SQL function that returns the first value of a sorted list, but I really don't know how to start? The function has only one parameter which is the listing number ( VRI.Listing_Number )

Using select statement, I have:

SELECT  TOP 1 
        --VRI.Listing_RID, VRI.Listing_Number, VRI.Listing_Price, CH.Old_Price, CH.Date_Time_Changed
        CH.Old_Price
FROM    dbo.View_Report_Information_Tables VRI WITH (NOLOCK) 
        INNER JOIN dbo.MLS_Change_History CH WITH (NOLOCK) ON
            VRI.Listing_RID = CH.Listing_RID
   开发者_开发问答     INNER JOIN dbo.MLS_Change_History_Type CHT WITH (NOLOCK) ON
            CH.Transaction_RID = CHT.Transaction_RID
WHERE   CHT.Change_Type_Display = 3 AND
        VRI.Listing_RID = CH.LISTING_RID
ORDER BY CH.Transaction_RID DESC

This sql query returns the last price changes from a list of prices.

I'm really new to sql, so I even don't understand quite well the syntax. For example, I looked up the CH.Old_Price, and I saw it TYPE_NAME is numeric, but it also has length and precision, scale.... What should my return value for this function?

Any idea?

Thanks,

---

CREATE FUNCTION dbo.FirstPriceChange(@Listing_Number int)
RETURNS MONEY
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN (
    SELECT  TOP 1 
            CH.Old_Price
    FROM    dbo.View_Report_Information_Tables VRI WITH (NOLOCK) 
            INNER JOIN dbo.MLS_Change_History CH WITH (NOLOCK) ON
                VRI.Listing_RID = CH.Listing_RID
            INNER JOIN dbo.MLS_Change_History_Type CHT WITH (NOLOCK) ON
                CH.Transaction_RID = CHT.Transaction_RID
    WHERE   CHT.Change_Type_Display = 3 AND
            VRI.Listing_RID = CH.LISTING_RID AND
            VRI.Listing_Number = @Listing_Number
    ORDER BY CH.Transaction_RID DESC
)
END
GO

Sample usage:

SELECT
    VRI.Listing_RID, VRI.col1, VRI.Col2,
    dbo.FirstPriceChange(VRI.Listing_Number) AS FirstPriceChange
FROM dbo.View_Report_Information_Tables VRI

Notes:

1. RETURNS MONEY it returns a money type
2. WITH RETURNS NULL ON NULL INPUT if the input is null, just return null
3. The returns is a single value, which is from the subquery, with the VRI.Listing_Number filter added

---

assuming the select statement you posted returns the correct data, the syntax of the function is rather simple:

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
-- Add the parameters for the function here
@Listing_Number int, 
)
RETURNS TABLE 
AS
RETURN 
(
-- Add the SELECT statement with parameter references here
)

you can also get this by right-clicking Functions in SSMS -> Object Explorer -> -> Programmability and choosing the right type of function. My sample above assumes that this returns an entire row of data (which seems to be the case). I also assumed that VRI.Listing_Number is an int.

---

SELECT TOP 1 FROM (

SELECT *
FROM    dbo.View_Report_Information_Tables VRI WITH (NOLOCK) 
        INNER JOIN dbo.MLS_Change_History CH WITH (NOLOCK) ON
            VRI.Listing_RID = CH.Listing_RID
        INNER JOIN dbo.MLS_Change_History_Type CHT WITH (NOLOCK) ON
            CH.Transaction_RID = CHT.Transaction_RID
WHERE   CHT.Change_Type_Display = 3 AND
        VRI.Listing_RID = CH.LISTING_RID
ORDER BY CH.Transaction_RID DESC

) AS `aaa`