query2 question

[techquest@fastmail.fm]

Ok, the query returns,max and min results like:
A,A1,A21,math10 0,88
A,A1,A21,math10 4,45
A,A1,A21,Chm100 ,86
A,A1,A21,chm102 ,44
A,A1,A22,math10 1,99
A,A1,A22,math10 0,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,math10 0,88 A,A1,A21,math10 4,45
A,A1,A21,math10 1,88
A,A1,A21,Chm100 ,86 A,A1,A21,chm102 ,44
A,A1,A22,math10 1,99 A,A1,A22,math10 0,86
A,A1,A22,math10 2,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,patin dex('%[0-9]%',exam)-1)
UNION
SELECT Name, Name1, Name2, MIN(Score) Score
FROM #Temp
GROUP BY Name, Name1, Name2, LEFT(Exam,PATIN DEX('%[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,PATIN DEX('%[0-9]%',Exam)-1) DESC, t.Score DESC


#Temp Table:
name,name1,name 2,exam,score
A,A1,A21,
A,A1,A21,math10 0,88
A,A1,A21,math10 1,56
A,A1,A21,
A,A1,A21,math10 2,67
A,A1,A21,
A,A1,A21,math10 4,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,math10 0,86
A,A1,A22,
A,A1,A22,math10 1,99
(skipping)
A,A2,A21,
A,A2,A21,math10 0,68
A,A2,A21,
A,A2,A21,math10 1,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