How to find a Maximum Column value in SQL which has a WHERE clause?

I am trying to write a Procedure in SQL. I want to print the Record with the “Maximum Score” from a Table after matching all date entries to a passed Input Argument. Right now, the code only prints all Records that correspond to the specified date. I’m not sure how to use the MAX() in this case.

/* =========================================================
Procedure to find the Highest Movie Rating on a particular date.
Given, an inpDate (input Date) it first looks up all Movie Reviews
of that particular date.
Then, it checks a StandardizedReviews Table (sTbl) and
finds the MAXIMUM adjusted_score with the
Highest Review Score (the variable adjusted_score)
========================================================= */
Use StandardizedReviews
Drop Procedure if exists HighestReview;
GO

CREATE PROCEDURE HighestReview @inpDate DATE AS (
        SELECT sTbl.adjusted_score "Max Score"
        FROM dbo.StandardizedReviews sTbl
        WHERE CAST(sTbl.date as DATE) = CAST(@inpDate as DATE)
        GROUP BY sTbl.date, sTbl.adjusted_score
)
GO
exec HighestReview '2020-03-05'
GO

Right now, my Output Looks like:

But I want it to just show the Record with the Score of 100. Thank You.

Answer

You would just wrap the adjusted_score values with MAX() AS ‘Max Score’ which will select the highest score only. You would also remove the reference to sTbl.adjusted_score in your group by and only order by the date:

CREATE PROCEDURE HighestReview @inpDate DATE AS (
    SELECT MAX(sTbl.adjusted_score) AS 'Max Score'
    FROM dbo.StandardizedReviews sTbl
    WHERE CAST(sTbl.Date AS DATE) = CAST(@inpDate AS DATE)
    GROUP BY sTbl.Date
)

Advertisement

Answer