How can i get distinct records with a minimum value

From: Miguel Nunes Pinto <j-miguel-pinto_at_telecom.pt>
Date: Tue, 26 Oct 1999 18:40:33 GMT
Message-ID: <3815f0c2.26754110@news.telecom.pt>

In a SQL query i need to get the records wich have a distinct combination of two fields (REQ_COD/SUB_REQ in the example), and at the same time the records on wich another field (STATUS) has the minimum value. I need to have a where clause as well. Example:

select req_cod, sub_req, min(status)
from ar_hist
where ((status < 2400 or status = 2402) and status >= 2300 and stat_ant < 2300)
group by cod_req, n_ord_sr, status

And i get some records like those:

REQ_COD	SUB_REQ	MIN(STATUS)
19990518557	1		2300
19990518557	1		2402

I just want this one: 19990518557	1		2300

Anyone knows why I get those two?

Lots of thanks in advance for any help.

Miguel Received on Tue Oct 26 1999 - 13:40:33 CDT