SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-48
Finding the Highest Value in a Row
The values in the table are as follows:
NAME SAT1 SAT2
-------- ----------- -----------
BROWN 480 520
BYSTROM 510 715
CHUNG 725 650
GOMEZ 780 610
HO 715 680
MCLAIN 600 520
MINSKY 400 510
PONG 790 720
SCHMIDT 580 590
SMITH 550 630
--- 10 row(s) selected.
You want to list the name and the highest of the two scores for each student. You can
use CASE to produce the result:
set list_count 0 ;
select name, CASE
when sat1 >= sat2 then sat1
else sat2
END
from scores;
detail name,
col 2 as I3 heading "Highest Score";
list all;
NAME Highest Score
-------- -------------
BROWN 520
BYSTROM 715
CHUNG 725
GOMEZ 780
HO 715
MCLAIN 600
MINSKY 510
PONG 790
SCHMIDT 590
SMITH 630
--- 10 row(s) selected.
For the EXPLAIN plan, see CASE for Finding the Highest Value in a Row on
page 6-35.