Min Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 29 Nov 2007 01:25 GMT

my data looks like the following

111 0 01/01/2007
111 0 02/01/2007
222 0 04/01/2007
222 0 05/01/2007
555 1 06/01/2007
666 0 06/01/2007

how can i retrieve the following???

111 0 01/01/2007
222 0 04/01/2007
555 1 06/01/2007
666 0 06/01/2007

my goal is to retrive distinct cols 1 and 2, and then the earliest date in
col 3 for distinct records in cols 1 and 2???

any suggestions??? I tried different ways of using min a, but was
unsuccessful. I can get max to work which returns the latest date, but when
i switch to min, I dont get any records.

Thanks in advance.

Reply to this Message

Rob Parker - 29 Nov 2007 02:06 GMT

Hi Jeff,

You do this via a totals query, grouping by the first two fields, and with
Min as the entry in the Totals row for the third (date) field. Your SQL
will be (with the appropriate table and field names):

SELECT Field1, Field2, Min(Field3) AS EarliestDate
FROM YourTableName
GROUP BY Field1, Field2;

HTH,

Rob

> my data looks like the following
>
[quoted text clipped - 20 lines]
>
> Thanks in advance.

Reply to this Message