Find the records based on group by Student ID

Question

Find the records based on group by Student ID

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
April 10, 2023 at 7:47 pm

#4171862

Data looks like below
Status 1 is Active and 2 is Clean
For one Student ID we may have one active, one clean status;
I want to find the records only with Clean status for a student ID.
Student Id Student_Status
12345 1
12345 2
12345 1
49568 1
99889 2
57758 2
88898 1
88898 1
09598 2
09598 2
From the above query i need the records with student id (99889, 57758 and 09598) as these records has status clean.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101119 More actions · Answer 1


SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MAX(CASE WHEN Student_Status = '2' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Student_Status <> '2' THEN 1 ELSE 0 END) = 0
--ORDER BY Student_ID

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

mcfarlandparkway

SSCertifiable

Points: 7699

April 10, 2023 at 8:46 pm

#4171887

ty so much , it worked

drew.allen SSC Guru Points: 76996 More actions · Answer 3

ScottPletcher wrote:

SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MAX(CASE WHEN Student_Status = '2' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Student_Status <> '2' THEN 1 ELSE 0 END) = 0
--ORDER BY Student_ID

I would have done the following.

SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MIN(Student_Status) = '2'
/*  If there are statuses greater than 2 uncomment the following line  */
-- AND  MAX(Student_Status) = '2'
--ORDER BY Student_ID

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 101119 More actions · Answer 4

drew.allen wrote:

ScottPletcher wrote:

SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MAX(CASE WHEN Student_Status = '2' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Student_Status <> '2' THEN 1 ELSE 0 END) = 0
--ORDER BY Student_ID

I would have done the following.

SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MIN(Student_Status) = '2'
/*  If there are statuses greater than 2 uncomment the following line  */
-- AND  MAX(Student_Status) = '2'
--ORDER BY Student_ID

Drew

I tend to write these things to allow any combination of values later, so I stick with individual checks. For example, suppose later they want to check for status 2 and status 1? Or (status 2 or status 1) and not status 3?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

ScottPletcher wrote:

I tend to write these things to allow any combination of values later, so I stick with individual checks. For example, suppose later they want to check for status 2 and status 1? Or (status 2 or status 1) and not status 3?

Heh... you beat me to it, Scott. I was just coming back to this to say "Really nicely done, Scott... especially since it's future-bullet-proof". It will also allow a conversion to IN and NOT IN if the requirements were to change to something like "can only have 2's and 3's", etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)