SEO

Ok, the query returns,max and min results like:
A,A1,A21,math100,88
A,A1,A21,math104,45
A,A1,A21,Chm100,86
A,A1,A21,chm102,44
A,A1,A22,math101,99
A,A1,A22,math100,86
so on...
which is ok.
how to return, max and min
result in this form instead (with exception that
all exams with same max or min
should show up):?.

A,A1,A21,math100,88 A,A1,A21,math104,45
A,A1,A21,math101,88
A,A1,A21,Chm100,86 A,A1,A21,chm102,44
A,A1,A22,math101,99 A,A1,A22,math100,86
A,A1,A22,math102,86
so on.




SELECT t.*
FROM #Temp t JOIN
(SELECT Name, Name1, Name2, MAX(Score) Score
FROM #Temp
GROUP BY Name, Name1, Name2, left(exam,patindex('%[0-9]%',exam)-1)
UNION
SELECT Name, Name1, Name2, MIN(Score) Score
FROM #Temp
GROUP BY Name, Name1, Name2, LEFT(Exam,PATINDEX('%[0-9]%',Exam)-1)) d
ON t.Name = d.Name AND t.Name1 = d.Name1
AND t.name2 = d.Name2 AND t.Score = d.Score
ORDER BY t.Name, t.Name1, t.Name2,
LEFT(Exam,PATINDEX('%[0-9]%',Exam)-1) DESC, t.Score DESC


#Temp Table:
name,name1,name2,exam,score
A,A1,A21,
A,A1,A21,math100,88
A,A1,A21,math101,56
A,A1,A21,
A,A1,A21,math102,67
A,A1,A21,
A,A1,A21,math104,45
A,A1,A21,Chm100,55
A,A1,A21,
A,A1,A21,chm101,86
A,A1,A21,chm102,44
A,A1,A21,
(skipping)
A,A1,A22,math100,86
A,A1,A22,
A,A1,A22,math101,99
(skipping)
A,A2,A21,
A,A2,A21,math100,68
A,A2,A21,
A,A2,A21,math101,92
(skipping)





--
Sent by techquest from fastmail subpart from fm
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com