sql server misc: create table pp
(
name char(10),
name1 char(10),
name2 char(10),
exam char(10),
score int
)

insert into pp values ('A', 'A1', 'A21', null, null)
insert into pp values ('A', 'A1', 'A21', 'Math 100', 88)
insert into pp values ('A', 'A1', 'A21', 'Math 101', 56)
insert into pp values ('A', 'A1', 'A21', null, null)
insert into pp values ('A', 'A1', 'A21', 'Math 102', 67)
insert into pp values ('A', 'A1', 'A21', null, null)
insert into pp values ('A', 'A1', 'A21', 'Math 104', 45)
insert into pp values ('A', 'A1', 'A21', 'Chm 100', 55)
insert into pp values ('A', 'A1', 'A21', null, null)
insert into pp values ('A', 'A1', 'A21', 'Chm 101', 86)
insert into pp values ('A', 'A1', 'A21', 'Chm 102', 44)
insert into pp values ('A', 'A1', 'A21', null, null)
insert into pp values ('A', 'A1', 'A22', 'Math 100', 86)
insert into pp values ('A', 'A1', 'A22', null, null)
insert into pp values ('A', 'A1', 'A22', 'Math 101', 99)
insert into pp values ('A', 'A1', 'A21', null, null)
insert into pp values ('A', 'A1', 'A21', 'Math 100', 68)
insert into pp values ('A', 'A1', 'A21', null, null)
insert into pp values ('A', 'A1', 'A21', 'Math 101', 92)



select name, name1, name2, exam, max(score) MaxScore, Min(score) MinScore
from pp
where exam is not null and score is not null
group by name, name1, name2, exam


"techquest@fastmail.fm" <u753143310@spawnkill.ip-mobilphone.net> wrote in
message news:l.1066880501.1423919677@x130-65-109-102.sjsu.edu...
[quoted text, click to view]

[quoted text, click to view]


Vishal:

The number after the exam is part of the exam name (course name), e.g. "Math
100", "Math 102", "Math 103" are three different exams (courses). This is
equivalent what is called (in some classes in India ) Math Part I, Math
Part II etc.

got slightly similar table.
#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)

How to select * in case of highest
score and lowest score grouped
by name,name1,name2 where exam
and score are not null
(empty fields) and by exam subject
'math', 'chm',etc?.

drop Table #Temp

result like:
A,A1,A21,math100,88
A,A1,A21,math104,45
A,A1,A21,Chm100,55
A,A1,A21,chm102,44
A,A1,A22,math101,99
etc.






--
Sent by techquest from fastmail within field fm
This is a spam protected message. Please answer with reference header.

Techquest,

From the expected result you 've posted i assume that anything starting from exam name as
math represents math examination. anything starting from chm represents chm examination.Im
not sure what is the number that is appended to each exam represents. Is my assumption
correct? or those number has some significance and represents different exams??

--sample table and records
create table #T
(name varchar(50),name1 varchar(50),name2 varchar(50),exam varchar(50),score int)
insert into #t
select 'A','A1','A21',null,null union all
select 'A','A1','A21','math100',88 union all
select 'A','A1','A21','math101',56 union all
select 'A','A1','A21',null,null union all
select 'A','A1','A21','math102',67 union all
select 'A','A1','A21',null,null union all
select 'A','A1','A21','math104',45 union all
select 'A','A1','A21','Chm100',55 union all
select 'A','A1','A21',null,null union all
select 'A','A1','A21','chm101',86 union all
select 'A','A1','A21','chm102',44 union all
select 'A','A1','A21',null,null union all
select 'A','A1','A22','math100',86 union all
select 'A','A1','A22',null,null union all
select 'A','A1','A22','math101',99 union all
select 'A','A2','A21',null,null union all
select 'A','A2','A21','math100',68 union all
select 'A','A2','A21',null,null union all
select 'A','A2','A21','math101',92

--Try following query.

select a.* from #t a join
(select name,name1,name2, substring (exam, 1, patindex ('%[0-9]%', exam)-1) exam,
max(score) 'score'
from #t
where (exam is not null and score is not null)
group by name,name1,name2,
substring (exam, 1, patindex ('%[0-9]%', exam)-1)
union all
select name,name1,name2, substring (exam, 1, patindex ('%[0-9]%', exam)-1) exam,
min(score) 'score'
from #t
where (exam is not null and score is not null)
group by name,name1,name2,
substring (exam, 1, patindex ('%[0-9]%', exam)-1)) b
on a.name = b.name and a.name1=b.name1
and a.name2=b.name2 and b.exam = substring (a.exam, 1, patindex ('%[0-9]%', a.exam)-1)
and a.score = b.score
order by 1 ,2 ,3,substring (a.exam, 1, patindex ('%[0-9]%', a.exam)-1),5 desc

/*
If there is no significance of the numbers that are appended to each exam just remove them
it unncessary makes query logic complicated. If it is required and is a part of atrributes
of
the table then seperate it into another column to normalize the table design.
*/

--Use following query if you want to group on exams as it is (inclusive of numbers).

select name,name1,name2, exam,
max(score) 'score'
from #t
where (exam is not null and score is not null)
group by name,name1,name2, exam
union all
select name,name1,name2, exam,
min(score) 'score'
from #t
where (exam is not null and score is not null)
group by name,name1,name2, exam
order by 1,2,3,4,5 desc

--
-Vishal

hmm, i never know that.

--
-Vishal