CODEWITHSUNDEEP
sqlsql-server-2008
chris
chris

Reputation: 2444

SQL - select only results having multiple entries

this seems simple but I cannot figure out how to do it or the proper description to correcltly google it :(

Briefly, have a table with:

PatientID | Date | Feature_of_Interest...

I'm wanting to plot some results for patients with multiple visits, when they have the feature of interest. No problem filtering out by feature of interest, but then I only want my resulting query to contain patients who have multiple entries. 

SELECT PatientID,Date,...
    FROM myTable
    WHERE Feature_Of_Interest is present
    AND (Filter out PatientID's that only appear once)

So - just not sure how to approach this. I tried doing:

WITH X AS (Above SELECT, Count(*),...,Group by PatientID)

Then re-running query, but it did not work. I can post that all out if needed, but am getting the impression I am approaching this completely backward, so will defer for now.

Using SQL Server 2008.

Upvotes: 2

Views: 2115

Answers (5)

τεκ
τεκ

Reputation: 3104

You'll want to join a subquery

JOIN (
    SELECT 
        PatientID
    FROM myTable 
    WHERE Feature_Of_Interest is present 
    GROUP BY PatientID
    HAVING COUNT(*) > 1
) s ON myTable.PatientID = s.PatientID

Upvotes: 1

Lucero
Lucero

Reputation: 60190

You should be able to get what you need by using a window function similar to this:

WITH ctePatient AS (
  SELECT PatientID, Date, SUM(1) OVER (PARTITION BY PatientID) Cnt
  FROM tblPatient
  WHERE Feature_Of_Interest = 1
)
SELECT * 
  FROM ctePatient
  WHERE Cnt > 1

Upvotes: 0

Chandu
Chandu

Reputation: 82893

Try this:

WITH qry AS
(
SELECT a.*,
COUNT(1) OVER(PARTITION BY PatientID) cnt
        FROM myTable  a
 WHERE Feature_Of_Interest = 'present '
)
SELECT *
  FROM qry
WHERE cnt >1

Upvotes: 2

BugFinder
BugFinder

Reputation: 17858

Quick answer as I head off to bed, so its untested code but, in short, you can use a sub query..

SELECT PatientID,Date,...
    FROM myTable
    WHERE Feature_Of_Interest is present
    AND patientid in (select PatientID, count(patientid) as counter
    FROM myTable
    WHERE Feature_Of_Interest is present group by patientid having counter>1)

Im surprised your attempt didnt work, it sounds a little like it should have, except you didnt say having count > 1 hence it probably just returned them all.

Upvotes: 0

Kerrek SB
Kerrek SB

Reputation: 476970

You could start with a counting query for visits:

SELECT PatientID, COUNT(*) as numvisits FROM myTable
GROUP BY PatientID HAVING(numvisits > 1);

Then you can base further queries off this one by joining.

Upvotes: 1

Related Questions