Get distinct rows for non-key values including keys without window functions-CodePudding

Consider the following tables and data:

CREATE TABLE Foo
(
    FooID int NOT NULL PRIMARY KEY,
    FooValue char(1) NOT NULL
);

CREATE TABLE Bar
(
    FooID int NOT NULL,
    BarID int NOT NULL,
    BarValue char(1),
    CONSTRAINT BarPK PRIMARY KEY (FooID, BarID),
    CONSTRAINT FooBar FOREIGN KEY (FooID) REFERENCES Foo(FooID)
);

INSERT INTO Foo(FooID, FooValue) VALUES
    (100, 'A'),
    (101, 'A'),
    (102, 'B'),
    (103, 'C'),
    (104, 'C');

INSERT INTO Bar(FooID, BarID, BarValue) VALUES
    (100, 1, 'X'),
    (100, 2, 'Y'),
    (101, 1, 'X'),
    (101, 2, 'Y'),
    (101, 3, 'Z'),
    (102, 1, 'X'),
    (103, 1, 'Z'),
    (104, 1, 'Z');

From this data I want a single row for each distinct combination of FooValue and BarValue together with their keys. I don't really care which keys are returned, so a possible result set could look like this:

FooID	BarID	FooValue	BarValue
100	1	A	X
100	2	A	Y
101	3	A	Z
102	1	B	X
104	1	C	Z

Usually I would do something like this:

WITH NumberedRows AS
    (
        SELECT ROW_NUMBER() OVER
                    (PARTITION BY F.FooValue, B.BarValue ORDER BY F.FooID DESC) AS nr,
                F.FooID, B.BarID, F.FooValue, B.BarValue
            FROM Foo F
                INNER JOIN Bar B
                    ON B.FooID = F.FooID
    )
SELECT FooID, BarID, FooValue, BarValue
    FROM NumberedRows
    WHERE nr = 1;

Unfortunately I can't use the above solution since I'm constained to not use a CTE or ROW_NUMBER() (or any other window function, so this similar question doesn't help). Is there another way I can get the desired result?

CodePudding user response：

If you can't use window functions, then you first need to find the maximum Foo.FooID value (as required from the window function clause ORDER BY F.FooID DESC ... WHERE rn = 1)

SELECT MAX(F.FooID) AS FooID, 
       B.BarID, 
       F.FooValue
FROM       Foo F
INNER JOIN Bar B
        ON B.FooID = F.FooID
GROUP BY B.BarID,
         F.FooValue

Once you get these values, you can join back with your two tables:

SELECT F.FooID, 
       B.BarID, 
       F.FooValue, 
       B.BarValue
FROM       Foo F
INNER JOIN Bar B
        ON B.FooID = F.FooID
INNER JOIN (SELECT MAX(F.FooID) AS FooID, 
                   B.BarID, 
                   F.FooValue
            FROM       Foo F
            INNER JOIN Bar B
                    ON B.FooID = F.FooID
            GROUP BY B.BarID,
                     F.FooValue           ) cte
        ON F.FooID    = cte.FooID
       AND B.BarID    = cte.BarID
       AND F.FooValue = cte.FooValue

Check the demo here.