SQL query to get lowest 2 values of a counted query selection (using db2)?

Question

Hi,

Imagine I already have a query that returns the following:

Col1 | Col2
------------
 A   |   2
 B   |   3
 C   |   3
 D   |   4
 E   |   8
    ...

Say I used something like this:

select Col1, count ( * ) as Col2 \ 
from ...
where ...
order by Col2 \ 
group by Col1 \ 

So now, all I want to select are (Col1, Col2) such that it returns the selections (a, b) and (c, d) where (b >= all (Col2)) and (d >= ((all (Col2)) - a)). So for the above example, it would return {(A, 2), (B, 3), (C, 3)}. How do I go about doing this?

Any help would be highly appreciated. Thanks.

Answer 1

If you're on version 7 or greater of db2 then there's a FETCH FIRST n ROWS ONLY construct. I don't know if there's any way for it to account for ties (I don't use db2). The clause is added after the ORDER BY clause.

Answer 2

Select Col1, Col2
From #Test
    Cross Join  (
                Select Min(T2.Col2) As Lowest, Min(T3.Col2) As SecondLowest
                From #Test As T2
                    Join #Test As T3
                        On T3.Col2 > T2.Col2
                ) As T4
Where #Test.Col2 In(T4.Lowest, T4.SecondLowest)

The idea here is to break up the request into its discrete pieces. First, find the lowest value. Then find the next value greater than the lowest value.

Answer 3

I don't have a DB2 handy to test, but I think what you want should go something like this, using olap functions, without the need of joining the table with itself:

SELECT Col1, Col2 
FROM (SELECT Col1, Col2, DENSE_RANK() OVER(ORDER BY Col2 Asc) As Rank
      FROM table) T
WHERE Rank <= 2
ORDER BY Col2 Asc

(The subselect is necessary as DB2 doesnt allo to use OLAP functions in a WHERE clause directly.)

This will give u the top 2 values including ties (replace DENSE_RANK() with RANK() to ignore ties or replace 2 by any number to get top-n values.