got a table with (class, name, exam, score) fields.

To find max(score) for exam that starts with letter 'm'
grouped by class and name,did:

select max(score) from #temp where exam like 'm%'
group by class,name

this work only for selecting max(score) from #temp.
How to select all columns and max(score) at the same time,
how to do select *,max(score) from #temp?.
when tried, it gave multiple reults, error.





--
Sent by 3 from yahoo piece of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com

Hi

It is better to post DDL (Create table statements) example data (as insert
statements), your attempted statements and expected output, rather than a
verbose description as this removes all ambiguities.

You could try

SELECT Class, Name, Exam, ( SELECT MAX(Score) FROM #Temp T WHERE T.Class =
D.Class AND T.Name = D.Name ) as MaxScore
FROM #Temp D
WHERE D.Exam like 'm%'

John


"alexqa2003@yahoo.com" <u128845214@spawnkill.ip-mobilphone.net> wrote in
message news:l.1065912301.1229156494@[63.127.215.130]...
> got a table with (class, name, exam, score) fields.
>
> To find max(score) for exam that starts with letter 'm'
> grouped by class and name,did:
>
> select max(score) from #temp where exam like 'm%'
> group by class,name
>
> this work only for selecting max(score) from #temp.
> How to select all columns and max(score) at the same time,
> how to do select *,max(score) from #temp?.
> when tried, it gave multiple reults, error.
>
>
>
>
>
> --
> Sent by 3 from yahoo piece of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com