How do I get earliest records using Min() and Where() SQL functions?

Question

I'm trying to pull records on the earliest date but I keep encountering an error. I assumed I could do this as part of a WHERE clause but I was wrong. How can I pull the data where it meets both criteria I have in my code currently?

left outer join 
(SELECT
[RID]
,[p_rid]
,[Budget Records.Budget ID]
,[Budget Records.Budget approval process]
,[Budget Records.Inflation base date]
,[Budget Records.Budget Stage]

,CAST(ISNULL([Budget Records.Total capital budget],0) as float) as [CAR Total capital budget]

FROM [FSG_DATA_TEAM].[dbo].[BR]
WHERE [Budget Records.Budget Stage] in ('Cost Plan 1') and min([Budget Records.Inflation base date])) as y

I get the output "An expression of non-boolean type specified in a context where a condition is expected, near ')' " but what I'm hoping for is the values to be returned contingent on the earliest inflation base date.

Answer 1

If using window analytic functions is supported by your database(seems SQL-Server), you can convert the above part of the query to :

left outer join 
(
    SELECT *
    (
     SELECT
          [RID]
          ,[p_rid]
          ,[Budget Records.Budget ID]
          ,[Budget Records.Budget approval process]
          ,[Budget Records.Inflation base date]
          ,row_number() over (order by [Budget Records.Inflation base date]) as rn
          ,[Budget Records.Budget Stage]
          ,CAST(ISNULL([Budget Records.Total capital budget],0) as float) as [CAR Total capital budget]
      FROM [FSG_DATA_TEAM].[dbo].[BR]
     WHERE [Budget Records.Budget Stage] in ('Cost Plan 1') 
    ) 
    WHERE rn = 1
) as y   

to restrict the results due to min of [Budget Records.Inflation base date] column.