I have set up the following DB2 table, which is intended to store codes and their corresponding descriptions, and to allow for multiple languages and/or countries.

DECLARE CODES TABLE (
CATEGORY INTEGER NOT NULL,
CODE CHAR(08) NOT NULL,
LANGUAGE CHAR(03) NOT NULL,
COUNTRY CHAR(03) NOT NULL,
DESCRIPTION VARCHAR(64) NOT NULL)

I added a unique primary key to this table as follows:

CATEGORY ASC,
CODE ASC,
LANGUAGE DESC,
COUNTRY DESC

OK, Fine so far. Now I complicate things by specifying that a description might be specific to a language and a country, a language only or it may be a defaulted American English description.

My problem is this. I want to obtain a complete list of codes for a given category, along with their descriptions. The following SQL:

SELECT CODE, DESCRIPTION
FROM CODES
WHERE CATEGORY = :CATEGORY
AND LANGUAGE IN (:LANGUAGE, ' ')
AND COUNTRY IN (:COUNTRY, ' ')

Could, in theory, return up to four rows per code. The requirement is to return one row per code, using the maximum language/country sub-key to select the appropriate description. The most efficient way to do this would be to get DB2 to somehow perform a skip-sequential read of the primary index, taking the first index per code. However, all my solutions so far have involved multiple levels of subselects and liberal use of the MAX() function, and don't strike me as efficient by any stretch of the imagination.

So can anyone point me towards a better solution.

SELECT CODE, DESCRIPTION
FROM CODES
WHERE CATEGORY = :CATEGORY
AND LANGUAGE IN (:LANGUAGE, ' ')
AND COUNTRY IN (:COUNTRY, ' ')
order by language, country
fetch first 1 row only

FETCH FIRST ROW ONLY would work if I want the first code/description only, but I want to return a result set containing all codes in the code category.

Right now I'm having to subselect MAX(LANGUAGE) for each category/code, then subselect MAX(COUNTRY) for each category/code/MAX(LANGUAGE). Once I have the complete key, I can read the description for that code. It looks ugly, at least I can draw some comfort in the fact that the two inner subselects access the index only.

Ok, If I now understand your problem correctly, you can do something like this :

select code, description from
(
SELECT CODE, DESCRIPTION, row_number() over(partition by code order by language desc, country desc)
FROM CODES
WHERE CATEGORY = :CATEGORY
AND LANGUAGE IN (:LANGUAGE, ' ')
AND COUNTRY IN (:COUNTRY, ' ')
) a
where rn = 1