Need help in query - urgent plz

Question

Need help in query - urgent plz

calibar2k

SSC Rookie

Points: 44
More actions
September 29, 2014 at 8:26 am

#297040

"Class""studentid" "is_std_prim""EnrolCheck""Subject1" "Subject2"
"ClassA" "10001""Y" "Eligible" "1""1"
"ClassA" "10002""N" "Eligible" "1""1"
"ClassB" "10003""Y" "Eligible" "1""0"
"ClassB" "10004""N" "Eligible" "1""0"
"ClassC" "10005""Y" "Eligible" "0""1"
"ClassC" "10006""N" "Eligible" "0""1"
"ClassC" "10010""N" "Eligible" "0""1"
"ClassD" "10007""Y" "Eligible" "1""0"
"ClassD" "10008""N" "Eligible" "0""1"
"ClassD" "10009""N" "Eligible" "1""0"
"ClassE" "10011""Y" "Eligible" "1""0"
I need a query to find the list of classes where students are enrolled for different subjects ?
Output:
"Class""studentid" "is_std_prim""EnrolCheck""Subject1" "Subject2"
"ClassD" "10007""Y" "Eligible" "1""0"
"ClassD" "10008""N" "Eligible" "0""1"
"ClassD" "10009""N" "Eligible" "1""0"

Viewing 3 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If it's urgent, I assume that you have tried something. Please show us what you've tried and we'll help you from that. It's considered polite to post the sample data with DDL and the data in the form of INSERT statements so we don't have to do additional work and we don't have to guess data types and probably give a wrong answer.

Could you explain why you're just expecting 3 rows as your result instead of 4?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61142 More actions · Answer 2

As Louis said, DDL and what you have tried will help us help you faster. That said, I think you are looking for this:

WITH

your_data AS

(SELECT *

FROM (values ('ClassA',10001,'Y','Eligible',1,1),

('ClassA',10002,'N','Eligible',1,1),

('ClassB',10003,'Y','Eligible',1,0),

('ClassB',10004,'N','Eligible',1,0),

('ClassC',10005,'Y','Eligible',0,1),

('ClassC',10006,'N','Eligible',0,1),

('ClassC',10010,'N','Eligible',0,1),

('ClassD',10007,'Y','Eligible',1,0),

('ClassD',10008,'N','Eligible',0,1),

('ClassD',10009,'N','Eligible',1,0),

('ClassE',10011,'Y','Eligible',1,0))

AS t (Class,studentid,is_std_prim,EnrolCheck,Subject1,Subject2)

),

diffs AS

(SELECT class

FROM

(SELECT *, rnk = rank() over (partition by class order by Subject1,Subject2)

FROM your_data

) diff_checker

WHERE rnk > 1

GROUP BY Class

)

SELECT y.*

FROM diffs d

CROSS APPLY your_data y

WHERE y.Class = d.Class;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001