Expecting a mytable where x can have the same value on multiple rows, but we only want to catch the first (or last) occurrence.
Target Qry:
select *
from my_table
where x IN ('A', 'B', 'C') <<some limit to get the first occurrence>>;
And an example mytable (first row is column names):
x y
A 5
A 1
A 3
B 3
B 2
C 8
Target Response (first occurrence):
x y
A 5
B 3
C 8
Can this may be archived using sub-queries?
Use distinct on:
select distinct on (x) x, y
from my_table
where x in ('A', 'B', 'C')
order by x, id
Important: it is unclear what you mean by first occurence, as your sampl table does not exhibit a column that can be used to order the records. I still assumed that such a column exists, and is called id.
You need a column defining order (ca be incremental order or date)
Last occurence:
select * from my_table a where not exists( select * from my_table b where a.x = b.x and
a.columnOrder < b.columnOrder)
First occurence:
select * from my_table a where not exists( select * from my_table b where a.x = b.x and
a.columnOrder > b.columnOrder)
As others have noted, unless you specify an ORDER BY, there is simply no concept of "first occurrence" - the order of records you get tomorrow may well be different to what you get today. Instead group and aggregate:
SELECT x, MAX(y) as y
FROM table
GROUP BY x
This produces the highest occurrence (equivalent of "first occurrence when order by descending")
It doesn't work for more than one aggregated column though; if you have two or more columns to retrieve, try:
SELECT x, y, z
FROM (SELECT x, y, z, ROW_NUMBER() OVER(PARTITION BY x ORDER BY y) r
WHERE r = 1
This produces every distinct x, the lowest y and the associated z
Some databases have a ROWID hidden column that you can use
SELECT x, y
FROM my_table P
WHERE ROWID = (SELECT MIN(ROWID) FROM my_table WHERE x = P.x)
