Query Question

[Jeff]

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 FIRST, but was
unsuccessful.

Thanks in advance.

 

[Smartin]

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 FIRST, but was
unsuccessful.

Thanks in advance.

This works for me:

SELECT Col1, Col2, MIN(Col3)
FROM Jeff
GROUP BY Col1, Col2;

FIRST works for me too, but your data may differ from the example
significantly enough that it may not work.

 

[Jason]

If you really want to group by both the 1st and 2nd fields try this:

SELECT tbl_Data.Field1, tbl_Data.Field2, First(tbl_Data.Field3) AS
FirstOfField3
FROM tbl_Data
GROUP BY tbl_Data.Field1, tbl_Data.Field2;

It will get you this:
Query1
Field1 Field2 FirstOfField3
111 0 1/1/2007
222 0 4/1/2007
555 1 6/1/2007
666 0 6/1/2007

If you want field1 to be truly distinct regardless of the variation in
Field2 try this:

SELECT tbl_Data.Field1, First(tbl_Data.Field2) AS FirstOfField2,
First(tbl_Data.Field3) AS FirstOfField3
FROM tbl_Data
GROUP BY tbl_Data.Field1;

It will get you this:
Query1
Field1 FirstOfField2 FirstOfField3
111 0 1/1/2007
222 0 4/1/2007
555 1 6/1/2007
666 0 6/1/2007

Both are the same based on the dataset you provided so it makes it tough to
determine exactly what you are wanting to do.