Advice on SQL query (mine seems to overcomplicate and even spits out scalar subquery error)

Question

I'm using SQL developer (oracle sql).

The query below spits out the error: "Subquery for one line returns more than one line"

Please advice on query logic. I don't expect anybody to solve it for me. Tell me where I am wrong with my thinking.

Given a table which gives the following data:

+---------+------+---------+------------+-----------+----------+
| PS_NAME | SIDE | WO_NUM  | LOT_PREFIX | PLAIN_LOT | AB_SPLIT |
+---------+------+---------+------------+-----------+----------+
| ...     | ..   | ...     |    ...     |  ...      |     .    |
| ANW216  | TO   | 5041092 |    VNAN    |  ANW216   |     x    |
| ANW217  | TO   | 4897524 |    VNAN    |  ANW217   |     x    |
| ANW218  | TO   | 5041093 |    VNAN    |  ANW218   |     x    |
| ANW219  | TO   | 5064069 |    VNAN    |  ANW219   |     x    |
| ANW219A | TO   | 5064097 |    VNAN    |  ANW219A  |     A    |
| ANW220  | TO   | 5064070 |    VNAN    |  ANW220   |     x    |
| ANW220A | TO   | 5064098 |    VNAN    |  ANW220A  |     A    |
| ANW221  | TO   | 5064071 |    VNAN    |  ANW221   |     x    |
| ANW221A | TO   | 5064099 |    VNAN    |  ANW221A  |     A    |
| ANW222  | TO   | 5064072 |    VNAN    |  ANW222   |     x    |
| ANW223  | TO   | 5062459 |    VNAN    |  ANW223   |     x    |
| ...     | ..   | ...     |    ...     |  ...      |     .    |
+---------+------+---------+------------+-----------+----------+

Desired result:

+---------+------+---------+------------+-----------+----------+
| PS_NAME | SIDE | WO_NUM  | LOT_PREFIX | PLAIN_LOT | AB_SPLIT |
+---------+------+---------+------------+-----------+----------+
| ...     | ..   | ...     |    ...     |  ...      |     .    |
| ANW216  | TO   | 5041092 |    VNAN    |  ANW216   |     x    |
| ANW217  | TO   | 4897524 |    VNAN    |  ANW217   |     x    |
| ANW218  | TO   | 5041093 |    VNAN    |  ANW218   |     x    |
| ANW219A | TO   | 5064097 |    VNAN    |  ANW219A  |     A    |
| ANW220A | TO   | 5064098 |    VNAN    |  ANW220A  |     A    |
| ANW221A | TO   | 5064099 |    VNAN    |  ANW221A  |     A    |
| ANW222  | TO   | 5064072 |    VNAN    |  ANW222   |     x    |
| ANW223  | TO   | 5062459 |    VNAN    |  ANW223   |     x    |
| ...     | ..   | ...     |    ...     |  ...      |     .    |
+---------+------+---------+------------+-----------+----------+

What I want to achieve: If there is a lot with A, then just give me the one with A; and do not show the non-A; So, give me ANW219A only (and ignore ANW219)

Here is the logic behind my query:

"Give me the PS_NAME,
   Case, when there is a truncated name (in table t2)
      which is also in table t3
   Then give me this lot with letter A
   Else give me the regular (non-A) lot"

From a first point of view this problem seems very easy but I just don't get where I want to be. And, my gut tells me that my query is probably way to complicated.

SELECT
            CASE
                WHEN (
                    SELECT
                        substr(lot, 0, length(lot) - 1)
                    FROM
                        table t2
                    WHERE
                            t1.wo_num = t2.wo_num
                        AND lot_prefix <> 'x'
                        AND side = 'TO'
                        AND ab_split <> 'x'
                ) IN (
                    SELECT
                        lot
                    FROM
                        table t3
                    WHERE
                            t1.wo_num = t3.wo_num
                        AND lot_prefix <> 'x'
                        AND side = 'TO'
                        AND ab_split = 'x'
                )
        --lot
                 THEN
            --t2.lot
                    'THEN'
                ELSE
            --t1.lot
                    'else'
            END ps_name
        FROM
            (
                SELECT
                    lot,
                    ps_date,
                    mcu,
                    wo_num,
                    srp3,
                    lot_core,
                    lot_prefix,
                    plain_lot,
                    ab_split
                FROM
                    table
                WHERE
                        lot_prefix <> 'x'
                    AND side = 'TO'
                    AND ab_split = 'x'
            ) t1

Answer 1

This might do it, depending on how the WHERE clause interacts with other rows in the table:

SELECT * 
FROM (
    SELECT t1.*,
        row_number() over (partition by substr(ps_name, 1, 6) order by substr(ps_name, 1, 6), wo_num desc) as rn
    FROM "table" t1
    WHERE Side = 'TO' AND lot_prefix<>'X'
) t
WHERE rn=1

Or to more explicitly match the stated logic:

SELECT * 
FROM (
    SELECT t1.*,
        row_number() over (
          partition by substr(ps_name, 1, 6) 
          order by case when substr(ps_name 7, 1) = 'A' then 0 else 1 end
         ) as rn
    FROM "table" t1
    WHERE Side = 'TO' AND lot_prefix<>'X'
) t
WHERE rn=1