sqldb2

SQL using in operator with fetch first rows only

I have a table with millions of records and want to search for names. Is it possible to say that only the first match of each record for name '12' and '23' should be outputted?

SELECT * FROM TABLE_A WHERE NAME IN ('12', '23')

Example:

TABLE_A

ID Name
1 12
2 23
3 12
4 24
5 25

Expected results should be only ids: 1 or 3 and 2

Solution

  • This can be done using the window function ROW_NUMBER() as follows :

    SELECT *
FROM (
  SELECT a.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id ) AS rn
  FROM TABLE_A a
  WHERE NAME IN ('12', '23')
) AS s
WHERE s.rn = 1;

    Demo here