0

I am trying to run a query in Azure Data Studio from a temporary table that I've created. However, in my SELECT statement, it does not recognize column name(s). The column names in the SELECT statement (i.e. FirstPlayer, SecondPlayer, Score) are underlined red and the query comes back as invalid.

Any idea on how to change the syntax to make this run?

IF OBJECT_ID('tempdb.dbo.#GameScores','U') IS NOT NULL
    DROP TABLE #GameScores;
GO

CREATE TABLE #GameScores
(
    FirstPlayer  VARCHAR(10),
    SecondPlayer VARCHAR(10),
    Score        INTEGER
);
GO

INSERT INTO #GameScores 
VALUES ('Joe','Ryan', 120),
       ('Sue', 'Jackie', 200),
       ('Ryan', 'Sue', 50),
       ('Ryan', 'Joe', 100);
GO

SELECT DISTINCT 
    FirstPlayer,
    SecondPlayer, 
    IF(Score = MAX(Score), MAX(Score) + 20, Score) AS Score
FROM 
    #GameScores
WHERE 
    SecondPlayer NOT LIKE "JO%"
    OR Points <> 100
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Tony Correia
  • 29
  • 6
  • 1
    There is no `if` but `iif`, but more importantly you should transform aggregated functions info [window functions](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15), something like `max(score) over (partition by FirstPlayer, SecondPlayer)`. – Nikola Markovinović Nov 19 '21 at 02:39
  • What does `MAX(Score)` refer to, the max of which rows? – Charlieface Nov 19 '21 at 13:47

1 Answers1

0

If you are having other columns in a select query, you need to have GROUP BY.

For making your query to work, you have to calculate the max(score) first and then apply them in the SELECT query as given below. also, you dont have points column in the table. I think you are referring to score column.

DECLARE @max_Score INT = (SELECT max(score) from #GameScores)
SELECT 
    DISTINCT FirstPlayer,
    SecondPlayer, 
    CASE WHEN Score = @max_Score THEN @max_Score+20 ELSE Score END AS Score
FROM #GameScores
WHERE SecondPlayer NOT LIKE 'JO%'
OR Score <> 100
FirstPlayer SecondPlayer Score
Joe Ryan 120
Ryan Sue 50
Sue Jackie 220
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58