PostgreSQL: SELECT MAX numeric value for entire table from multiple columns

Question

I have a table similar to this (3 rows, 4 columns: id, name, gradeA, gradeB):

id    name    gradeA    gradeB
------------------------------
1     David   59        78
2     Anna    66        92
3     David   22        89

I'm looking for a query that will give me 1 numeric property for the highest grade by in the whole table by condition. for example:

What is the highest grade for any student named David?

The answer should be { maxGrade: 89 }

Note: grades are stored as varchar values and should be cast to numeric values for comparison

How come "grades are stored as varchar values and should be cast to numeric values for comparison"? I'd use int data type instead. — jarlh
– jarlh, Commented Aug 18, 2020 at 8:42
Thats a 'given'. I did not configure the table and they are 'natively' used as string values in other places. — Maor Barazani
– Maor Barazani, Commented Aug 18, 2020 at 8:45
above JSON is a filed in your table or you have a table with 4 fields — Akhilesh Mishra
– Akhilesh Mishra, Commented Aug 18, 2020 at 8:47
The above example reflects a table with 3 rows and 4 columns: id, name, gradeA, gradeB — Maor Barazani
– Maor Barazani, Commented Aug 18, 2020 at 8:48
"grades are stored as varchar values" - do you have a chance to fix that broken data model? Never store numbers as varchars. Just don't — user330315
– user330315, Commented Aug 18, 2020 at 9:00

user330315 · Accepted Answer · 2020-08-18 09:00:15Z

6

You need to combine max() with greatest()

select max(greatest(gradea::int, gradeb::int)) as max_grade
from the_table
where name = 'David';

If you need a JSON result (because you wrote: result should be {maxGrade: 89}), you can wrap that query and convert the row to JSON:

select to_jsonb(t) 
from (
  select max(greatest(gradea::int, gradeb::int)) as "maxGrade"
  from the_table
  where name = 'David'
) t;

answered Aug 18, 2020 at 9:00

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

@user11350468 because without the max() you get two rows with. One with 89 and one with 78

user11350468 Over a year ago

Yes, Got it, I forgot about the duplicate names.., Thanks for your time

Gordon Linoff · Accepted Answer · 2020-08-18 12:33:54Z

1

I would recommend a lateral join:

select max(grade::int) as max_grade
from t cross join lateral
     (values (gradea), (gradeb)) v(grade)
where name = 'David';

In particular, this works if any of the grades are NULL.

It can also easily be tweaked to get which grade is the maximum:

select v.*
from t cross join lateral
     (values ('a', gradea), ('b', gradeb)) v(which, grade)
where name = 'David'
order by grade::int desc
limit 1

answered Aug 18, 2020 at 12:33

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Maor Barazani Over a year ago

What would be the expected output of the 2nd 'which' query?

Gordon Linoff Over a year ago

Why don't you run it to see? It puts the name of the columns that has the maximum.

user11350468 · Accepted Answer · 2020-08-30 12:50:49Z

1

Try the below:

We shall achieve with GREATEST and MAX:

We can make use of GREATEST to return the large value between the columns grade A and grade B for every user record
And use MAX to return the maximum grade if more than user matches with the same name.

SELECT 
  MAX(GREATEST(gradeA::integer, gradeB::integer)) as maxGrade 
FROM 
  "table_name" 
WHERE 
  "table_name"."name" = 'David';

edited Aug 30, 2020 at 12:50

answered Aug 18, 2020 at 8:58

user11350468

1,4471 gold badge10 silver badges24 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: SELECT MAX numeric value for entire table from multiple columns

3 Answers 3

2 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Related