SQL/MP Query Guide

Retrieving Data: How to Write Queries

HP NonStop SQL/MP Query Guide—524488-003

1-47

Finding the Highest Value in a Row

These are the values in the table:

NAME AGE DEPT CARS

-------- ------ ----------- ------

BROWN 30 50 1

CHANG 38 50 2

GONZALES 22 50 1

38 50 2

KAPOOR 28 50 1

LEBLANC 25 50 0

PETSKI 23 40 4

YAMASAKI 24 40 2

--- 8 row(s) selected.

You need to compute the number of employees who have one car, two cars, and so

on. Using CASE and SUM, you can get the result:

set list_count 0 ;

select SUM(CASE when cars = 0 then 1 else 0 END),

SUM(CASE when cars = 1 then 1 else 0 END),

SUM(CASE when cars between 2 and 3 then 1 else 0 END),

SUM(CASE when cars > 3 then 1 else 0 END)

from emp;

detail col 1 as I1 heading “None”,

col 2 as I1 heading “One”,

col 3 as I1 heading “Two or Three”,

col 4 as I1 heading “More Than Three”;

list all;

None One Two or Three More Than Three

---- --- ------------ ---------------

1 3 3 1

See CASE With Aggregates on page 6-34 for the EXPLAIN plan. For information on

SUM, see Aggregate Functions in Predicates on page 1-41.

Finding the Highest Value in a Row

Suppose that you need to select the largest (or smallest) value from multiple columns

in each row of a table. For example, each row in a table of students contains the

student’s name and scholastic aptitude test scores (SATs) for the past two years. All

SAT scores are numeric, and none are null. The primary key is NAME.