[quoted text, click to view] u359375000@spawnkill.ip-mobilphone.net (alexsql2000@yahoo.com) wrote in message news:<l.1066087533.1806060791@[63.127.215.130]>...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
>
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
>
> Finding max(score) for exam started with 'm'
> grouped by class and name.
>
> select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
>
> from #table D
> DROP TABLE #table
> GO
>
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
>
> If exists(select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
>
> Did not resolve it.
Your explanation isn't completely clear (your query doesn't match your
column names, and you don't give the exact error message you're
getting), but it looks like this should simply work:
select class, name, exam, max(score)
from #table
where exam like 'm%'
group by class, name, exam
If this isn't what you need, then consider posting working CREATE
TABLE statements, along with INSERTs to add your sample data, and the
output you expect.
