Geek Answers Handbook

Standard SQL to replace GREATEST () in MySQL query

I have a MySQL query that uses the GREATEST() function, and I want to rewrite it in standard ANSI SQL to work in other databases. I know that GREATEST is supported by most SQL databases, but I will probably run the query in Hive, which supports things like CASE but not GREATEST.

Can anyone think of an elegant way to rewrite this query without using GREATEST() ?

Thanks!

 select greatest(play,play_25,play_50,play_75,play_100) as play, greatest(play_25,play_50,play_75,play_100) as play_25, greatest(play_50,play_75,play_100) as play_50, greatest(play_75,play_100) as play_75, play_100 as play_100 from video_buckets
+4
source share
2 answers

This should work, although I'm not sure if it can be called "elegant":

 SELECT CASE WHEN play_25 > play THEN play_25 ELSE play END AS play, play_25, play_50, play_75, play_100 FROM ( SELECT play, CASE WHEN play_50 > play_25 THEN play_50 ELSE play_25 END AS play_25, play_50, play_75, play_100 FROM ( SELECT play, play_25, CASE WHEN play_75 > play_50 THEN play_75 ELSE play_50 END AS play_50, play_75, play_100 FROM ( SELECT play, play_25, play_50, CASE WHEN play_100 > play_75 THEN play_100 ELSE play_75 END AS play_75, play_100 FROM video_buckets ) s ) s ) s
+2
source

This will not work in MySQL, but checks as a full SQL-92

 SELECT (SELECT MAX(c) FROM (VALUES(play), (play_25), (play_50), (play_75), (play_100)) T (c)) AS play, (SELECT MAX(c) FROM (VALUES (play_25), (play_50), (play_75), (play_100)) T (c)) AS play_25 FROM video_buckets
+4
source

More articles:


All Articles