Query Question

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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.
 
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.
Click to expand...

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.
 
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.
 
Back
Top