Group by clause

[glac]

I have this result whith this sql

--------------------------------------
NAME Start_date End_date min(score)
--------------------------------------
JOHN 02/2002 03/2002 150
JOHN 03/2002 04/2002 55
JOHN 04/2002 05/2002 100

from 2 tables

SELECT t1.name, t2.start_date, t2.end_date, min(t2.score) from t1,t2
where t1.name = t2.name
group by t1.name, t2.start_date, t2.end_date

But I just want have one record whith the period of the min score:

--------------------------------------
NAME Start_date End_date min(score)
--------------------------------------
JOHN 03/2002 04/2002 55


Have you an idea ?

[skhanal]

You have to use derived table.

Also all the columns returned are in t2, you don't need to join to t1.


select t2.name, t2.start_date, t2.end_date
from t2 as t2
join
(select name, min(score) as minscore
from t2
group by name) as t
on t2.name = t.name
and t2.score = t.minscore

[vinitasinha]

It may be late.... but this could be another solution

SELECT DISTINCT T2.NAME, T2.START_DATE, T2.END_DATE, T2.SCORE
FROM T2
WHERE (name,score) IN
(SELECT name,min(score)
FROM T2
GROUP BY NAME
)

- Vinita

[baldudka]

Try this one and let me know

SELECT DISTINCT T2.NAME, T2.START_DATE, T2.END_DATE, T2.SCORE
FROM T2
WHERE t1.name=t2.name
AND T2.score=(select min(score) from T2)
/