thx mate
i got it

Brian,

Depending on the data in the table, that is more akin to RANK than
ROW_NUMBER.  That is, you might get multiple rows for each empid using
that method - if there are mutliple rows with the same MIN(col2) for a
given empid.

Of course, if this isn't the case, then it works just fine.

-Chris

But you _can_ also do without the GROUP BY.  For example like here:

Data:
-----
$ db2 "select * from t1"

A           B
----------- -----------
         1           2
         1           3
         1           4
         1           1
         2           1
         2           2
         2           8

This will now get the first two rows for each group:

SELECT o.*
FROM   t1 AS o,
      LATERAL ( SELECT *
                FROM   t1 AS i
                WHERE  o.a = i.a
                ORDER BY i.b
                FETCH FIRST 2 ROWS ONLY ) AS x
WHERE x.b = o.b

A           B
----------- -----------
         1           2
         1           3
         2           1
         2           2

 4 record(s) selected.

Granted, the GROUP BY is much, much nicer.  But I just remembered that Serge
mentioned LATERAL once and I thought this should work as well. ;-)

FY:
(SELECT 1, 2 UNION ALL
        SELECT 1, 3 UNION ALL
        SELECT 1, 4 UNION ALL
        SELECT 1, 1 UNION ALL
        SELECT 2, 1 UNION ALL
        SELECT 2, 2 UNION ALL
        SELECT 2, 8) AS B(c1, c2)
Is SQL server syntax. SQL Standard syntax is:
(VALUES (1, 2),
        (1, 3),
        (1, 1),
        (2, 1),
        (2, 2),
        (2, 8)) AS B(c1, c2)