How to return results that have multiple records tied to it, filtering out the rows that only have 1 result

Question

PayID	userclass	category	paydate
90	111	7	1/1/2022
91	111	7	3/1/2022
92	222	8	2/1/2022
93	333	8	2/1/2022
94	444	9	3/15/2022
95	444	9	4/1/2022

So I want to write a SQL query that allows me to result with the records associated with payIDs 90,91,94 and 95 because I want records associated with multiple paydates of the same category within the same userclass.

-- So far I can get the entirety of the queried results as show in the example above

With my structure being something like:

SELECT
  p.payID,
  p.userclass,
  pc.category,
  p.paydate
FROM
  pay p 
    INNER JOIN paycategory pc
       ON p.categoryID = pc.categoryID

Which shows everything but not filtered down to the 4 records I want to be output.
i.e

PayID	userclass	category	paydate
90	111	7	1/1/2022
91	111	7	3/1/2022
94	444	9	3/15/2022
95	444	9	4/1/2022

I think I need to use the count function or something along that line but I'm having trouble understanding it.

Answer 1

You can use a CTE using DENSE_RANK() along with EXISTS to achieve this:

WITH cte AS (
  SELECT  
    p.payID,
    p.userclass,
    pc.category,
    p.paydate,
    DENSE_RANK() OVER (PARTITION BY p.userclass, pc.category ORDER BY payID) AS dr
  FROM pay p 
  INNER JOIN paycategory pc ON p.categoryID = pc.categoryID)
SELECT 
    a.payID,
    a.userclass,
    a.category,
    a.paydate
  FROM cte a 
  WHERE EXISTS (
    SELECT 1 FROM cte b WHERE a.userclass = b.userclass and a.category = b.category
    AND dr > 1)

Result:

| payID | userclass | category | paydate   |
|-------|-----------|----------|-----------|
| 90    | 111       | 7        | 1/1/2022  |
| 91    | 111       | 7        | 3/1/2022  |
| 94    | 444       | 9        | 3/15/2022 |
| 95    | 444       | 9        | 4/1/2022  |

Fiddle here.