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

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

CodePudding user response：

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.

CodePudding user response：

We use window function count() over() and select the results where there are multiple records per userclass and category.

select  PayID
       ,userclass
       ,category
       ,paydate
from 
(
select  *
       ,count(*) over(partition by userclass, category) as cnt
from    t
) t
where   cnt > 1

Fiddle