Query Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 29 Nov 2007 00:24 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 FIRST, but was
unsuccessful.

Thanks in advance.

Reply to this Message

Jeje - 29 Nov 2007 03:37 GMT

try something like:

select ID, Nb, Date
from table T
where Date = (Select min(Date) from Table t2
where t.id = t2.id)

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

Reply to this Message

David Portas - 29 Nov 2007 07:12 GMT

> my data looks like the following
>
[quoted text clipped - 19 lines]
>
> 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)

Signature

David Portas

Reply to this Message