Unique rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Unique rows

Author

Topic

Specialist
Starting Member

4 Posts

Posted - 2010-08-09 : 16:32:11

I am trying to write a query that will return the following: All students in one particular school who were a) fifth graders at the close of the school year 2009-2010 (fyEOY = 2010) and who were b)also continuously enrolled at this one particular school since Kindergarten. I can easily retrieve this list with multiple rows for each student such that each row corresponds to a different grade level; however, I'd like to have only one row (corresponding to the Student_Id) for each student.

The columns (in no particular order) I believe I need to use from our student enrollment table are:
- Student_Id
- School_num (Where School_num = '0909')
- fyEOY
- Grade_Level (written as 'KG','01','02' etc)
- Last_Name
- First_Name

Is this possible without using GROUP BY? Any help is appreciated!

Thank You

Specialist
Starting Member

4 Posts

Posted - 2010-08-09 : 16:43:34

[as a follow up to my OP) I'm quite new to using SQL and adapted a query used for another purpose in our dept to try to understand how to retrieve the data that I am particularly interested in viewing.

I have end up with two different queries and am not sure how to interpret them so that I understand why the number of returned rows varies:

1. (returns 96 rows)
SELECT Student_Id, Last_Name, First_Name, Grade_Level

FROM Student_Enrollment_History
WHERE (fyEOY = 2010 AND Grade_Level = '05' AND School_Num = '0909')
AND (SELECT COUNT(DISTINCT Grade_Level)
FROM Student_Enrollment_History
WHERE Days_Roll_YTD > 150 --An arbitrary number of days enrolled in order to be considered enrolled for each school year
AND Entry_Date <= CONVERT(DATETIME, CONVERT(VARCHAR, fyBOY) + '-09-30 00:00:00', 102) --Started on or before Sep 30
AND Grade_Level IN ('KG','01','02','03','04','05') --These are the grade levels continuously enrolled
) = 6
ORDER BY Last_Name

2. (returns 78 rows)
SELECT Student_Id, Last_Name, First_Name, fyEOY, Grade_Level

FROM Student_Enrollment_History
WHERE School_num = '0909' AND fyEOY = 2010 AND Grade_Level = '05'
AND Days_Roll_YTD > 150 --An arbitrary number of days enrolled in order to be considered enrolled for each school year
AND Entry_Date <= CONVERT(DATETIME, CONVERT(VARCHAR, fyBOY) + '-09-30 00:00:00', 102)
AND Grade_Level IN ('KG','01','02','03','04','05')

ORDER BY Last_Name, fyEOY

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-10 : 15:07:01

may be this:-


SELECT sh.Student_Id, Last_Name, First_Name, Grade_Level 
FROM	Student_Enrollment_History sh
JOIN (SELECT Student_Id
FROM Student_Enrollment_History
WHERE Days_Roll_YTD > 150 --An arbitrary number of days enrolled in order to be considered enrolled for each school year
AND Entry_Date <= CONVERT(DATETIME, CONVERT(VARCHAR, fyBOY) + '-09-30 00:00:00', 102) --Started on or before Sep 30
AND Grade_Level IN ('KG','01','02','03','04','05') --These are the grade levels continuously enrolled
GROUP BY Student_Id
HAVING COUNT(DISTINCT Grade_Level) = 6)sh1
ON sh1.Student_Id = sh.Student_Id
WHERE	(sh.fyEOY = 2010 AND sh.Grade_Level = '05' AND sh.School_Num = '0909')
ORDER BY Last_Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Specialist
Starting Member

4 Posts

Posted - 2010-08-11 : 10:40:00

Thank you, visakh16 ... The query works!

Could you explain to me how the JOIN clause functions since this query pulls data from only one table (Student_Enrollment_History)? I can tell that "sh" and "sh1" are important to the JOIN clause, it's just that I didn't know JOIN could be used when retrieving data from one table.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 11:00:01

this is called self join where you use same table twice in join by giving it different alises (sh,sh1 in example above) foor distinguishing each.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 11:01:59

and in above case what i'm doing is first grouping the data in table by means of studentid for all gradelevels required and taking distinct count and retrieving ones with dist count 6 which means they've been there for all 6 grade levels. then i join this list with original table to retrieve their other details like Last_Name, First_Name etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Specialist
Starting Member

4 Posts

Posted - 2010-08-11 : 11:18:41

Oh yes, of course, self-join! This is great. Thanks for lending a hand with this and helping me learn.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 11:39:47

no probs...you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources