I have a SQL query question that I have no idea whether it's achievable.

My database table structure: I have one table (TABLE A) is linked with second table (TABLE B) with the relationship of ONE TO MANY.

Problem Scenario: The ROW A in TABLE A is linked with 2 rows (ROWS A & ROW B) in TABLE B with different value in COLUMN A . The next row ROW B in TABLE A is linked with only 1 row (ROW C) in TABLE B. (Kindly refer to the following screenshot)

What I want:

• I want to retrieve the result that show column A value in TABLE B and primary key value of the row in TABLE A.
• Not allow multiple row with same primary key. In other word, only one column value should retrieve from TABLE B (Even there's multiple rows in TABLE B that linked with one row in TABLE A). Due to this reason, when select the row in TABLE B, the column A that contain value of 2 will be taken first, the remaining rows will ignore. If the column A of each row in TABLE B doesn't contain the value of 2, then only it will select the row with value of 1.

Based on the above scenario, the expected result show look like this:

The following is the sample data:

-- create a table
CREATE TABLE tableA 
(
    id INTEGER PRIMARY KEY
);

CREATE TABLE tableB 
(
    id INTEGER PRIMARY KEY,
    columnA INTEGER , 
    fkid INTEGER,
    FOREIGN KEY (fkid) REFERENCES tableA(id)
);

-- insert some values
INSERT INTO tableA VALUES (1);
INSERT INTO tableA VALUES (2);

INSERT INTO tableB VALUES (1, 1, 1);
INSERT INTO tableB VALUES (2, 2, 1);
INSERT INTO tableB VALUES (3, 1, 2); 

CodePudding user response：

We can use ROW_NUMBER here along with a join, and impose the ordering rules you want:

WITH cte AS (
    SELECT a.id, b.columnA,
           ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.columnA DESC) rn
    FROM tableA a
    INNER JOIN tableB b ON b.fkid = a.id
)

SELECT id, columnA
FROM cte
WHERE rn = 1;

Demo