Keep the faith

A frequent query request is that, when you've used MAX or MIN to find the highest, latest or otherwise most favoured row(s), you want the values of the other columns in that row.

Often a suggested answer is the use of a sub-query. But this overlooks one of those less well known features in Oracle SQL, the KEEP clause.

Consider the test case:


create table test_max 
  (id number,
   colour varchar2(20));
   
insert into test_max values (1,'Red');
insert into test_max values (2,'Orange');
insert into test_max values (3,'Yellow');
insert into test_max values (4,'Green');
insert into test_max values (5,'Blue');
insert into test_max values (6,'Purple');
insert into test_max values (7,'Black');
insert into test_max values (8,'White');
insert into test_max values (9,'Yellow');
insert into test_max values (10,'Yellow');

The maximum value of the 'colour' column is 'Yellow'. To make it a bit trickier, I've included three rows for the colour Yellow, with the IDs 3, 9 and 10. This allows us to see what happens if more than one row matches that maximum value.

select max(colour), 

       min(id) keep (dense_rank last order by colour asc) min_id,

       max(id) keep (dense_rank last order by colour asc) max_id,

       count(id) keep (dense_rank last 

                     order by colour asc) cnt_col,

       count(id) keep (dense_rank last 

                     order by colour asc, id desc) cnt_id

from test_max;

MAX(COLOUR)   MIN_ID  MAX_ID CNT_COL  CNT_ID

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

Yellow             3      10       3       1

The query tells us that the highest colour is yellow, and of the 'yellow' rows, the lowest id is 3, the highest is 10 and that there are three in total. Whether you use DENSE_RANK LAST and ORDER BY ... ASC or DENSE_RANK FIRST and ORDER BY ... DESC is a readability issue, and should be judged on a case-by-case basis.

The final count demonstrates that, by adding further ordering criteria, it is possible to detail down to an individual result.

If you have Tom Kyte's STRAGG installed, you can use that too. [My own implementation is odd in that I expect the delimiter to be concatenated into the parameter value, rather than coding it into the aggregate function.]

I haven't found a way to pull in the new 11g LISTAGG function which has its own syntactic peculiarites.

select stragg(id||',') keep (dense_rank last 

                         order by colour asc) cnt_col,

       stragg(id||',') keep (dense_rank last 

                         order by colour asc, id desc) cnt_id

from test_max

CNT_COL    CNT_ID

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

3,9,10,    3,