A recursive query instead of a control-break scenario?

[CliffDowell]

In a single table, I have a set of records (one table) for people with (potentially) multiple records per individual person. For each person, those multiple records have a date and severity code (possible 1 through 5 but not requirements on what values other than 1 through 5 and many don't have a 1). As a result set, the record(s) pertaining to one person need to be together but overall, we need the whole list of people that do have a code = 1 to come first in the listing with their complete record set (remainder of rows maybe not a code 1) before the next person in the list.

If I simply ORDER BY, the people and the codes, the person/codes line up but I can't get the overall grouping of those "person-sets" with “code = 1” to bubble up to the top. With enough CTEs, I could build a column based on a rank and read the rank to sort but it seems unnecessarily cumbersome.

This is obviously a classic control-break processing structure that has lived in RPG for 40+ years. In terms of getting it into an SQL statement, it sounds like a job for a recursive query but I’m not sure what variety to use. Many of the recursive options (CONNECT BY varieties) are not thoroughly documented or seem to have only a specific application. The Connect_by_ISLEAF seems plausible as the “code” records are “leaves of the person” maybe. Most examples I’ve found are from Oracle but not ANSI standard for pull into DB2.

Has anyone used a recursive query to replace a more "complex for SQL" control-break scenario?

Many thanks,
Cliff

[TedHolt]

Recursion sounds like overkill to me, Cliff. When I need to force records that meet a condition to the top of the result set, I use a CASE statement.

declare global temporary table MyData
( Person char (10), Sev integer, SomeDate date);

insert into session.Mydata (Person, Sev) values
( 'Joe', 1),
( 'Joe', 3),
( 'Joe', 5),
( 'Bob', 2),
( 'Bob', 4),
( 'Sam', 1),
( 'Doug', 3),
( 'Doug', 4),
( 'Pete', 2),
( 'Jack', 1);

With Sev1 as
  (Select Person
     from session.MyData
   where Sev = 1)
select d.*
  from session.MyData as d
     left join Sev1 as s
       on (d.Person = s.Person)
    order by case when s.Person is not null then 1 else 2 end,
           d.Person, d.Sev;

[CliffDowell]

That worked perfectly and fit the bill for elegant, too! Thanks Ted!

[TedHolt]

Glad to hear it, Cliff.

BTW, I should have said CASE expression, not CASE statement.