CODEWITHSUNDEEP
sqlsql-servergroup-bypartition-by
shockwave
shockwave

Reputation: 3272

SQL filter rows based without using Group by

I have a query which will perform joins over 6 tables and fetches various columns based on a condition. I want to add an extra filter condition which will give me only those members who have a count(distinct dateCaptured)>30. I'm able to get the list of members who satisfy this condition using Group by and having. But I don't want to group by other column names because of this one condition. Do I need to use PARTITION BY in this case.

Sample TABLE a

+-----+------------+--------------+
| Id  | Identifier | DateCaptured |
+-----+------------+--------------+
| 1   |      05548 | 2017-09-01   |
| 2   |      05548 | 2017-09-01   |
| 3   |      05548 | 2017-09-01   |
| 4   |      05548 | 2017-09-02   |
| 5   |      05548 | 2017-09-03   |
| 6   |      05548 | 2017-09-04   |
| 7   |      37348 | 2017-08-15   |
| 8   |      37348 | 2017-08-15   |
| .   |            |              |
| .   |            |              |
| .   |            |              |
| 54  |      37348 | 2017-10-15   |
+-----+------------+--------------+

Query

SELECT  a.value,
        b.value, c.value,
        d.value
        FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id

Assume Table a has more than 30 records for Identifier 37348. How can I get only this Identifier for the above query.

These are the patients i'm interested in for the above SELECT.

SELECT  a.Identifier,count(DISTINCT DateCaptured)
    FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id 
    GROUP BY Identifier
    HAVING count(DISTINCT DateCaptured)>30

Upvotes: 0

Views: 211

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If the multiple rows really are in tableA, then you can do:

SELECT a.value, b.value, c.value, d.value
FROM (SELECT a.*, COUNT(*) OVER (PARTITION BY id) as cnt
      FROM a
     ) a INNER JOIN
     b 
     ON a.Id = b.id INNER JOIN 
     c 
     ON a.Id = c.Id AND s.Invalid = 0 INNER JOIN 
     d 
     ON a.Id = d.Id
WHERE a.cnt > 30;

Note: If you still need count(distinct) you can do:

SELECT a.value, b.value, c.value, d.value
FROM (SELECT a.*, SUM(CASE WHEN seqnum = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id) as cnt
      FROM (SELECT a.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY DateCaptured) as seqnum
            FROM a
           ) a
     ) a INNER JOIN
     b 
     ON a.Id = b.id INNER JOIN 
     c 
     ON a.Id = c.Id AND s.Invalid = 0 INNER JOIN 
     d 
     ON a.Id = d.Id
WHERE a.cnt > 30;

Upvotes: 1

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

SELECT  a.value,
        b.value, c.value,
        d.value
        FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id 
WHERE a.Identifier IN (SELECT  a1.Identifier  
    FROM Table a1
    GROUP BY a1.Identifier HAVING count(DISTINCT a1.DateCaptured)>30)

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

WITH cte as (
    SELECT  a.Identifier
    FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id 
    GROUP BY Identifier
    HAVING count(DISTINCT DateCaptured) > 30
)
SELECT  a.value,
        b.value, c.value,
        d.value
FROM Table a
INNER JOIN Table b on a.Id=b.id
INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
INNER JOIN Table d on a.Id=d.Id 
INNER JOIN cte on cte.Identifier = a.Identifier

Upvotes: 1

Related Questions