hi

Hi,
I have a StudentMark table (StudentID int , SubjectID int, Mark int,
Semester tinyint)
The pass criteria for each student is given as follow
1. He should pass in all the 6 subjects (pass mark = 40)
2. Condition 1 +
1. Avg of any 4 subjects is 40% + Avg any 3 70%
or
2. Avg of any 5 subjects is 50% + avg of any 2 is 80%
or
3. Avg of all the subjects is 60%
can i do it in a single Query ? I had done it using table vars?
Thanks in Advance
LaraBased on your narrative, it sounds like it can be handled in a single query.
But without details on table structures, sample data & expected results,
others can only guess ( for details see: www.aspfaq.com/5006 )
Anith

Help: Instance index

I need to create an instance index for a table group by studentid and classid

CREATE TABLE Table1

(ClassID varchar(10),

StudentID varchar(10),

Fee1 money,

fee2 money,

fee3 money

)

INSERT INTO Table1

VALUES ('02003', '00001', 20,10,15)

INSERT INTO Table1

VALUES ('02003', '00001', 25,15,15)

INSERT INTO Table1

VALUES ('02005', '00001', 10,10,15)

INSERT INTO Table1

VALUES ('02005', '00004', 20,10,15)

INSERT INTO Table1

VALUES ('02005', '00004', 20,10,15)

INSERT INTO Table1

VALUES ('02005', '00004', 20,10,15)

SELECT * FROM Table1

the output i'd like to see is :

ClassIDStudentIDFee1Fee2Fee3Index

020030000120.0010.0015.001

020030000125.0015.0015.002

020050000110.0010.0015.001

020050000420.0010.0015.001

020050000420.0010.0015.002

020050000420.0010.0015.003

Any suggestions on what method to use? Thanks.

Here it is,

Code Snippet

SELECT

ClassID,StudentID,Fee1, Fee2, Fee3,

ROW_NUMBER() over (Partition By ClassId, StudentId Order By ClassId, StudentId) as Index

FROM

Table1

|||

Use ROW_NUMBER()...something like this (didn't test it)

select

ClassID

,StudentID

,Fee1

,Fee2

,Fee3

,RANK() OVER (PARTITION BY ClassID, StudentID ORDER BY Fee1) AS 'Index'

from

Table1

|||

Thanks!!