Friday, March 9, 2012

error(2)

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.

--
Sent by from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comu359375000@.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.

Simon|||Hi

The following are untested by you may want to try:

If exists(select * from #table T where T.exame like 'm%' )
select *, (select max(T.result)
from #table T
where T.class = D.class
and T.name = D.name
and exame like 'm%' ) as mx
from #table D

Or try the following without the check:
select D.*, T.Mx
from #table D JOIN

(select Class, Name, max(result) as MX
from #table
WHERE exame like 'm%'
GROUP BY Class, Name ) T ON T.class = D.class
and T.name = D.name
WHERE D.exame like 'm%'

John

"alexsql2000@.yahoo.com" <u359375000@.spawnkill.ip-mobilphone.net> 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.
>
>
> --
> Sent by from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

No comments:

Post a Comment