help in query - 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

Transact-SQL (2005)

help in query

Author

Topic

fateh232000
Starting Member

3 Posts

Posted - 2010-12-05 : 22:23:42

Hello all;

I have a students marks table, which has two columns (studentId, mark),and school_class which has(classId, studentId).
what I need is how to distribute students between classes based on their marks, i.e each class should have excellent, good, and poor students.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-05 : 22:34:31

Provide some sample data and expected output.

fateh232000
Starting Member

3 Posts

Posted - 2010-12-05 : 23:07:17

Thanks pk_bohra for your response, and here is sample data:

student_mark:
studentId mark
1 92
2 55
3 65
4 70
5 88
6 76

I need to distribute those students to 2 school classes based on their marks:
classId studentId
1 5
1 4
1 2
2 1
2 3
2 6

possibility to distribute them in more classes.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-05 : 23:16:38

Here you go :

Declare @Student table
(StudentID int,
Mark int
)

Insert into @Student
Select 1,92 union
Select 2, 55 union
Select 3, 65 union
Select 4, 70 union
Select 5, 88 union
Select 6, 76

Select
Case when Srno % 2 = 0 Then 1 Else 2 End As ClassID,
StudentId from
(
Select Row_Number() over (Order by Mark desc) as Srno,
* from @Student) As SubTab
Order by ClassID

Regards,
Bohra

Sachin.Nand

2937 Posts

Posted - 2010-12-05 : 23:28:31

quote:
Originally posted by fateh232000

Hello all;

I have a students marks table, which has two columns (studentId, mark),and school_class which has(classId, studentId).
what I need is how to distribute students between classes based on their marks, i.e each class should have excellent, good, and poor students.

But what if the no of students are odd.Something like this


Declare @Student table
(StudentID int,
Mark int
)

Insert into @Student
Select 1,92 union
Select 2, 55 union
Select 3, 65 union
Select 4, 70 union
Select 5, 88 union
Select 6, 76 union
Select 7, 78 

select * from @Student order by Mark desc

PBUH

fateh232000
Starting Member

3 Posts

Posted - 2010-12-07 : 18:11:29

Thanks a lot Sachin.Nand and pk_bohra for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources