Re: Help with Select Distinct

select s1.first_name,s1.last_name,s1.grades

    from students s1, students s2 where
    s1.last_name=s2.last_name and
    s1.first_name=s2.first_name and
   ( s1.grade>s2.grade or
    (s1.grade=s2.grade and s1.rowid<s2.rowid) )

That way you get all of them. If the grade is equal then it doesn't matter which one you get. The rowid comparison is if you get an exact name and grade repeat. Then we arbitrarily take the "earlier" on in the table. If you want generic SQL then you have to modify it a bit, since rowid is an Oracle specific feature.

Jim

"Tuan Nguyen" <tnguyen_at_at-macktech-dot.com> wrote in message news:u0j80u4qo0kcj0jk97a6hg4puo97qai5v2_at_4ax.com...
> Hi all
>
> Supposed I have a table called STUDENTS with these type of data
>
>
> SQL> select * from STUDENTS;
>
> FIRST_NAME LAST_NAME GRADES
> ------------------------ ------------------------- ----------
> Cody Scow 90
> David Allen 84
> Cody Scow 79
> Joe Abad 92
>
> SQL>
>
> How would I select from this table so I would only get one Cody Scow
> with the lower grade returned? Something like
>
>
> FIRST_NAME LAST_NAME GRADES
> ------------------------ ------------------------- ----------
> Cody Scow 79
> David Allen 84
> Joe Abad 92
>
> Thanks for any help!!!
>
> -Tuan
>
Received on Tue Nov 27 2001 - 21:07:17 CST