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
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;