SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-51
Ignoring the Largest and Smallest Values in a Set
Ignoring the Largest and Smallest Values in a Set
You can use CASE with SUM to retrieve the values in the table used in the previous
example and ignore the largest and smallest values in the table:
set list_count 0 ;
select x.value
from data x, data y
group by x.value
having SUM (CASE when y.value <= x.value then 1 else 0 END) > 1
AND SUM (CASE when y.value >= x.value then 1 else 0 END) > 1;
detail col 1 as I4 heading "Value";
list all;
Value
-----
10
12
20
24
38
40
48
50
52
66
67
80
--- 12 row(s) selected.
For the EXPLAIN plan, see CASE for Ignoring the Largest and Smallest Values in a
Set on page 6-38. For information on SUM, see Aggregate Functions in Predicates on
page 1-41.
Combining Data From More Than One Table
You can join two tables to form a new table. When tables are joined, each new row is
formed by concatenating two rows, one from each of the original tables. This type of
query is called a join query.
To join tables, name the tables (or views) in the FROM clause of a SELECT statement.
When tables are joined, each new row is formed by concatenating the rows from each
of the original tables. The values in the paired rows must satisfy the join condition. For
example, this query joins two tables together on the column DEPT_NO:
SELECT EMPLOYEE_NAME, DEPT_NAME
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPT_NO = DEPT.DEPT_NO ;
The predicate EMPLOYEE.DEPT_NO = DEPT.DEPT_NO is called a join predicate. A
join query contains zero or more join predicates that identify and compare columns