I have 2 Tables StudentEnrollmentDetails and StudentExamAnalysis. Using these 2 Table I need following output.
SchoolId Rank StudentName Percentage
1 1 ABC 70
1 2 ABD 69.56
1 3 ABC 69
2 1 ABC 71
2 2 ABD 68.56
2 3 ABC 63
I have a basic query. I need to generate rank on the Fly.
SELECT SED.SchoolId, @s:=@s+1 serial_number, SAED.*
FROM StudentAnswerExamData SAED INNER JOIN StudentEnrollmentDetails SED ON SED.StudentEnrollmentDetailsId = SAED.StudentEnrollmentDetailsId
INNER JOIN (SELECT @s:= 0) AS s
WHERE SED.ExamCategoryId = 2 AND SED.ExamId = 2 AND SED.Standard = 5 AND SED.CompanyId = 2
ORDER BY SED.SchoolId, SAED.Percentage DESC;
Table Definition
Table: studentenrollmentdetails
FIELD TYPE
StudentEnrollmentDetailsId BIGINT(20)
StudentId BIGINT(20)
StudentRegNo BIGINT(20)
ExamCategoryId INT(11)
ExamId BIGINT(20)
SchoolId BIGINT(20)
SourceId BIGINT(20)
SourceAccountId BIGINT(20)
Standard INT(11)
Amount DECIMAL(10,2)
IsOptionEnabled TINYINT(1)
ExamSessionId BIGINT(20)
EnrollmentFormPath VARCHAR(300)
Division VARCHAR(10)
TransactionTime DATETIME
CompanyId INT(11)
STATUS INT(11)
UploadedBy BIGINT(20)
FileId BIGINT(20)
LastModifiedBy BIGINT(20)
LastModifiedByTime DATETIME
SchoolName VARCHAR(100)
SchoolLocation VARCHAR(100)
MarkForDelete TINYINT(1)
Table: studentanswerexamdata
Column Information
FIELD TYPE
StudentAnswerExamDataId BIGINT(20)
StudentId BIGINT(20)
StudentEnrollmentDetailsId BIGINT(20)
ExamCategoryId INT(10)
ExamId BIGINT(20)
Standard INT(10)
CompanyId INT(10)
UploadedById BIGINT(20)
UploadedDateTime DATETIME
ModifiedById BIGINT(20)
ModifiedDateTime DATETIME
MarkForDelete TINYINT(1)
SeatNo BIGINT(20)
NoOfCorrect INT(11)
NoOfInCorrect INT(11)
NoOfMissing INT(11)
MarksScored DECIMAL(6,2)
Percentage DECIMAL(5,2)
SchoolRank INT(11)
DistrictRank INT(11)
StateRank INT(11)
NationalRank INT
Try this:
SELECT *, (IF(@schoolId=@schoolId:=A.schoolId, @s:=@s+1, @s:=1)) serial_number
FROM (SELECT SED.SchoolId, SAED.*
FROM StudentAnswerExamData SAED
INNER JOIN StudentEnrollmentDetails SED ON SED.StudentEnrollmentDetailsId = SAED.StudentEnrollmentDetailsId
WHERE SED.ExamCategoryId = 2 AND SED.ExamId = 2 AND SED.Standard = 5 AND
SED.CompanyId = 2
ORDER BY SED.SchoolId, SAED.Percentage DESC
) AS A, (SELECT @s:=1, @schoolId:=0) AS s;
Check this Link SQL FIDDLE DEMO
OUTPUT
| SCHOOLID | STUDENTANSWEREXAMDATAID | STUDENTID | STUDENTENROLLMENTDETAILSID | EXAMCATEGORYID | EXAMID | STANDARD | COMPANYID | UPLOADEDBYID | UPLOADEDDATETIME | MODIFIEDBYID | MODIFIEDDATETIME | MARKFORDELETE | SEATNO | NOOFCORRECT | NOOFINCORRECT | NOOFMISSING | MARKSSCORED | PERCENTAGE | SCHOOLRANK | DISTRICTRANK | STATERANK | NATIONALRANK | PROCESSEDSTATUS | FILEPATH | @S:=1 | @SCHOOLID:=0 | SERIAL_NUMBER |
|----------|-------------------------|-----------|----------------------------|----------------|--------|----------|-----------|--------------|--------------------------------|--------------|--------------------------------|---------------|------------|-------------|---------------|-------------|-------------|------------|------------|--------------|-----------|--------------|-----------------|----------|-------|--------------|---------------|
| 23 | 4 | 1546 | 2044 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1023052901 | 38 | 37 | 0 | 113 | 75.33 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 54 | 6 | 1557 | 2055 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1054052901 | 25 | 46 | 4 | 96 | 64 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 56 | 16 | 1550 | 2048 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1056052903 | 36 | 39 | 0 | 111 | 74 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 56 | 15 | 1549 | 2047 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1056052902 | 30 | 43 | 2 | 103 | 68.67 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 2 |
| 56 | 13 | 1548 | 2046 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1056052901 | 27 | 48 | 0 | 102 | 68 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 3 |
| 74 | 7 | 1551 | 2049 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1074052901 | 21 | 54 | 0 | 96 | 64 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 78 | 8 | 1558 | 2056 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1078052901 | 37 | 38 | 0 | 112 | 74.67 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 108 | 9 | 1547 | 2045 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1108052901 | 26 | 46 | 3 | 98 | 65.33 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 114 | 10 | 1555 | 2053 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1114052901 | 28 | 47 | 0 | 103 | 68.67 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 133 | 5 | 1556 | 2054 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1133052901 | 44 | 29 | 2 | 117 | 78 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 160 | 3 | 1552 | 2050 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1160052901 | 25 | 50 | 0 | 100 | 66.67 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 164 | 14 | 1553 | 2051 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1164052901 | 32 | 43 | 0 | 107 | 71.33 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 170 | 17 | 1554 | 2052 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1170052901 | 12 | 15 | 48 | 39 | 26 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 324 | 12 | 1560 | 2058 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1324052902 | 41 | 32 | 2 | 114 | 76 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 1 |
| 324 | 11 | 1559 | 2057 | 2 | 2 | 5 | 2 | 5 | January, 28 2014 15:01:13+0000 | 1 | January, 29 2014 11:19:35+0000 | 0 | 1324052901 | 29 | 33 | 13 | 91 | 60.67 | -1 | -1 | -1 | -1 | 1 | (null) | 1 | 0 | 2 |