Query Question

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.try something like:
select ID, Nb, Date
from table T
where Date = (Select min(Date) from Table t2
where t.id = t2.id)
"Jeff" <findjeffajob@.emailias.com> wrote in message
news:0468D8F6-CABC-4758-A5E3-864723DDD630@.microsoft.com...
> 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.|||"Jeff" <findjeffajob@.emailias.com> wrote in message
news:0468D8F6-CABC-4758-A5E3-864723DDD630@.microsoft.com...
> 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:
CREATE TABLE tbl (col1 INT, col2 INT, col3 DATETIME);
INSERT INTO tbl VALUES (111, 0, '20070101');
INSERT INTO tbl VALUES (111, 0, '20070201');
INSERT INTO tbl VALUES (222, 0, '20070401');
INSERT INTO tbl VALUES (222, 0, '20070501');
INSERT INTO tbl VALUES (555, 1, '20070601');
INSERT INTO tbl VALUES (666, 0, '20070601');
SELECT col1, col2, MIN(col3) col3
FROM tbl
GROUP BY col1, col2
ORDER BY col1, col2 ;
col1 col2 col3
-- -- --
111 0 2007-01-01 00:00:00.000
222 0 2007-04-01 00:00:00.000
555 1 2007-06-01 00:00:00.000
666 0 2007-06-01 00:00:00.000
(4 row(s) affected)
David Portassql