Hi,
Here's an interesting query problem i have. Would greatly appreciate
some help on this 1.

Table: Student
Cols: Fname(not null), Lname, Marks(not null)
No primary key.

I want the highest marks of a given frist name whose last name is not
null.

Sample:

Fname Lname Marks
-------------------------------
Tom Evans 10
Tom Evans 20
Tom 30
Harry Potter 80
Harry Potter 30
Harry Potter 20
Dick 30
------------------------------

Expected RS:

Fname Marks
----------------------------
Tom 20
Harry 80

Thanks in advance,
Indranil.

dey.indranil@gmail.com wrote:
> Hi,
> Here's an interesting query problem i have. Would greatly appreciate
> some help on this 1.
>
> Table: Student
> Cols: Fname(not null), Lname, Marks(not null)
> No primary key.
>
> I want the highest marks of a given frist name whose last name is not
> null.
>
> Sample:
>
> Fname Lname Marks
> -------------------------------
> Tom Evans 10
> Tom Evans 20
> Tom 30
> Harry Potter 80
> Harry Potter 30
> Harry Potter 20
> Dick 30
> ------------------------------
>
> Expected RS:
>
> Fname Marks
> ----------------------------
> Tom 20
> Harry 80
>
> Thanks in advance,
> Indranil.
>

select fname, max(marks) from student where lname is not null group by
fname;