1
WITH R1 AS
(
    // some complex query
),
R2 AS 
(
    // uses R1
    // another complex query
),
// here I already have dataset with Id, Name, ProductReference and other columns
R3 AS 
(
    select  Name, ProductReference, Count(*) as Count  
    from Products 
    group by Name, ProductReference
    having Count(*) > 1
)

Now I need to filter R2 results based on R3, so that if Name and Reference combinations ARE present in R2 then only select where ID is minimum, discard rest of them

R2
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
2       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data
5       n1          someguid1           some data
6       n1          someguid2           some data

R3
Id      Name        ProductReference    Count
1       n1          someguid1           4


Expected Output
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data
4
  • Just have an R4 CTE that uses the output of R2 and R3?
    – Philᵀᴹ
    Commented Feb 24, 2020 at 15:16
  • @Philᵀᴹ I am confused with how I am going to join R2 and R3, I can't use inner join as it only give me common between R2 and R3, but I want all from R2 except the duplicates,and if duplicate only select first based on min id Commented Feb 24, 2020 at 15:22
  • The count of n1 is 4, but you have it as 3 - typo?
    – Vérace
    Commented Feb 24, 2020 at 15:35
  • @Vérace yes, fixed now, thanks Commented Feb 24, 2020 at 15:37

4 Answers 4

1

The following query produces the results you expect, with a minor correction to your stated R2 Results:

WITH
-- Given R2 Values, with minor correction
R2 AS (
    SELECT * FROM (
        VALUES
             (1,'n1','someguid1','some data')
            ,(2,'n1','someguid1','some data')
            ,(3,'n2','someguid2','some data')
            ,(4,'n3','someguid1','some data')
            ,(5,'n1','someguid1','some data')
            ,(6,'n1','someguid1','some data') -- changed someguid2 to someguid1
    ) V(Id, Name, ProductReferences, OtherColumns)

),
-- Given R3 Values
R3 AS (
    SELECT * FROM (
        VALUES
             (1,'n1','someguid1',4)

    ) V(Id, Name, ProductReferences, Count)
)
SELECT * 
FROM R2 
WHERE 
    -- match name and reference in R3
    EXISTS (SELECT 1 FROM R3 WHERE R2.Name = R3.Name AND R2.ProductReferences = R3.ProductReferences)
    -- is the lowest ID for name and reference for R2
    AND ID IN (SELECT MIN(ID) FROM R2 GROUP BY Name, ProductReferences)
UNION ALL
SELECT * 
FROM R2 
WHERE
    -- does not match any name and reference from R2
    NOT EXISTS (SELECT 1 FROM R3 WHERE R2.Name = R3.Name AND R2.ProductReferences = R3.ProductReferences)
ORDER BY Id

Note: If your R3 is intended to be derived solely form R2 (versus Products as illustrated in your example), you can skip R3 completely with the following query:

WITH
-- Given R2 Values, with minor correction
R2 AS (
    SELECT * FROM (
        VALUES
             (1,'n1','someguid1','some data')
            ,(2,'n1','someguid1','some data')
            ,(3,'n2','someguid2','some data')
            ,(4,'n3','someguid1','some data')
            ,(5,'n1','someguid1','some data')
            ,(6,'n1','someguid1','some data') -- change someguid2 to someguid2
    ) V(Id, Name, ProductReferences, OtherColumns)

)
SELECT * 
FROM R2 
WHERE 
    ID IN (SELECT MIN(ID) FROM R2 GROUP BY Name, ProductReferences)
0

if im not mistaken, you can get the result you want in R3, simply use

select min(id) as Id,Name,ProductReference
from Products
group by Name,ProductReference

the result will return exactly what you are looking for in your example, n1 will be grouped to its minimum id, thus the result set will be

Id,Name,PR,OC 1,n1,...,... 3,n2,...,... 4,n3,...,...

3
  • Thanks but then I can't select other columns, I get "...is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Commented Feb 24, 2020 at 15:33
  • if I use Aggregate functions with all columns then i am not sure how to get only top row based on min id Commented Feb 24, 2020 at 15:38
  • you can try R4 that is selecting from R3 (without aggregated functions) and use the group by min what aggregated functions do you use? Commented Feb 24, 2020 at 20:33
0

Guessing a bit:

select min(id), name, ProductReference 
from R2
where exists ( 
    select 1 
    from R3 
    where R2.name = R3.name 
      and R2.ProductReference = R3.ProductReference
)
group by name, ProductReference
union all
select id, name, ProductReference 
from R2
where not exists ( 
    select 1 
    from R3 
    where R2.name = R3.name 
      and R2.ProductReference = R3.ProductReference
)
;
-1

What you want is something like this (see below and fiddle here for complete DDL and DML):

(Also, I made the other_columns column all different so they couldn't be GROUP BY-ed. which could lead to incorrect results - at least, that's what I found...).

SELECT * FROM r2
WHERE id = (SELECT id FROM r3)
AND name = (SELECT name FROM r3)
AND product_reference = (SELECT product_reference FROM r3)
UNION
SELECT * FROM r2 
WHERE name != (SELECT name FROM r3)
ORDER BY id, name;

Result:

id  name    product_reference   other_columns
1   n1              someguid1   blah1
3   n2              someguid2   blah3
4   n3              someguid1   blah4

=============== DDL and DML ==============

Table r2:

CREATE TABLE r2
(
  id INTEGER NOT NULL,
  name VARCHAR (5) NOT NULL,
  product_reference VARCHAR (20) NOT NULL,
  other_columns VARCHAR (20) NOT NULL
);

Populate:

INSERT INTO r2 
VALUES
(1, 'n1', 'someguid1', 'blah1'),
(2, 'n1', 'someguid1', 'blah2'),
(3, 'n2', 'someguid2', 'blah3'),
(4, 'n3', 'someguid1', 'blah4'),
(5, 'n1', 'someguid1', 'blah5'),
(6, 'n1', 'someguid2', 'blah6');

Table r3:

CREATE TABLE r3
(
  id INTEGER NOT NULL,
  name VARCHAR (5) NOT NULL,
  product_reference VARCHAR (20) NOT NULL,
  pcount INTEGER NOT NULL
);

INSERT INTO r3 VALUES (1, 'n1', 'someguid1', 4);

Query:

SELECT * FROM r2
WHERE id = (SELECT id FROM r3)
AND name = (SELECT name FROM r3)
AND product_reference = (SELECT product_reference FROM r3)
UNION
SELECT * FROM r2 
WHERE name != (SELECT name FROM r3)
ORDER BY id, name;

Result:

id  name    product_reference   other_columns
1   n1  someguid1   blah1
3   n2  someguid2   blah3
4   n3  someguid1   blah4
4
  • p.s. in future, could you please provide tables as DDL (CREATE TABLE foo...) and your data as DML (INSERT INTO foo VALUES (...). It helps those trying to help you, provides one single source of truth (the question itself) and removes the need to guess (see @Lennart 's answer). You can always do this as a fiddle.
    – Vérace
    Commented Feb 25, 2020 at 14:53
  • You solution assumes a single value for the results of R3, which works for the sample data, but would fail if R3 had more than 1 result. Additionally, if Name was not unique (if there could be row with the same name but difference product references) your query would not produce the correct results.
    – Graham
    Commented May 31, 2020 at 7:35
  • @Graham - well, I can only really work with the sample data as given. If the OP said that there could be more than one 1 result for R3, yeah, sure, it mightn't work - but I didn't have that info at the time. Anway, it's a Bank Holiday Sunday morning here ( 8-) ) and I don't have time to pursue this at the minute - but I am pissed off that I got downvoted for what was a reasonable effort which provided the correct answer - and I went to the trouble of constructing DDL and DML for the problem. Ah well, I suppose some people are never happy!
    – Vérace
    Commented May 31, 2020 at 9:04
  • in my opinion it was not a reasonable assumption that R3 only ever produce a single result. However it was use of independent sub-queries for Name and ProductReference that earned the down vote. They are to be treated as a combination. Your answer works in this narrow example, however so does omitting them from the first part of the UNION because of the ID. That said, OP's R3 query actually doesn't include ID, so I wouldn't rely on it.
    – Graham
    Commented May 31, 2020 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.