SQL- How to get MAX value with multiple columns BUT I only have 1 row

Question

I'm trying to get the greatest value of just 1 row (mult columns). All of the tutorials I've watched are for multiple rows.

How can have just the greatest value (4943) returned? Thanks!

Answer 1

AFAIK SQL Server (the screenshot looks like SMSS, please add the SQL Server tag to your question if you're using SQL Server or the tag for the DBMS you're actually using) has no function for that. You need to write your own function or use a CASE ... END similar to:

SELECT CASE
         WHEN pop_under >= pop_10_to
              AND pop_under >= pop_20_to
              ...
              AND pop_under >= pop_80_p
           THEN pop_under
         WHEN pop_10_to >= pop_under
              AND pop_10_to >= pop_20_to
              ...
              AND pop_10_to >= pop_80_p
           THEN pop_10_to
         ...
         WHEN pop_80_p >= pop_under
              AND pop_80_p >= pop_10_to
              ...
              AND pop_80_p >= pop_70_to
           THEN pop_80_p
        END greatest
      FROM elbat;

(Can and should possibly be refined here and there (e.g. handling NULLs), it's just to demonstrate the idea.)

Answer 2

Clearly, if possible this table should be normalized into a more suitable structure where this problem would not have occurred, such as

CREATE TABLE Test
(
   [Id] INT IDENTITY(1, 1)
   [Pop] INT,
   [From] INT,
   [To] INT
)

This would make the solution trivial.

However, using the current structure, we can solve this by using the UNPIVOT operator. Assume that your table above is called test and that there are 4 columns of similar type called p1, p2, p3, p4. Then the following query will accomplish the request

SELECT MAX(pop) 
FROM (SELECT * FROM test WHERE Id = [InsertIdHere]) AS TableFilteredToDesiredOneRow
UNPIVOT
(
    pop
    FOR ThePs IN (p1, p2, p3, p4)
) AS U

This eliminates the need to for any bug prone large conditional blocks.