Help with SQL Query

Hi all,

I have a table as follows:
ID1 ID2 Property1 Property2 Date
1 23 P1 P2 01/04/2004
1 24 P22 P3 02/04/2004
2 25 P13 P22 01/05/2004
2 26 P34 P76 31/01/2004

What I want to retrieve is this:
For each value of ID1, I want to retrieve ONE record from all the ones sharing the same ID1 value, and this record is the earlist record of these.

For example, if I have the data above, I want to retrieve the following result

1 23 P1 P2 01/04/2004
2 26 P34 P76 31/01/2004

Any ideas how to do this? I've tried various GROUP BY and JOINS but can't seem to get anywhere near :(

Thanks!There are 2 steps involved:

1) What is the earliest date per ID1?

select id1, min(date) from table group by id1;

2) Get the records where the id1 and date values are in the list generated by query 1:

select * from table
where (id1, date) in (select id1, min(date) from table group by id1);|||Originally posted by andrewst
There are 2 steps involved:

1) What is the earliest date per ID1?

select id1, min(date) from table group by id1;

2) Get the records where the id1 and date values are in the list generated by query 1:

select * from table
where (id1, date) in (select id1, min(date) from table group by id1); Just an observation, but this can produce more than one row per value of ID1 if there are "ties" with the lowest date value.

-PatP|||and whaddya bet the poster's database won't support the

... where (a,b) in (select x,y...)

structure

(what's that called, anyway? a row expression?)

as far as i know, only oracle does

yeah, it's in sql-92, but since when does every database vendor support sql-92, eh|||So what is the alternative? I guess maybe:

select *
from table,
(select id1, min(date) mindate from table group by id1) v
where t.id1 = v.id1
and t.date = v.mindate;

...or is in-line view support unusual too?|||unusual? kinda

i call that a derived table

mysql certainly would have trouble with it, eh|||alternatives? two

correlated subquery:select ID1
, ID2
, Property1
, Property2
, myDate
from myTable zzz
where myDate =
( select min(myDate)
from myTable
where ID1 = zzz.ID1 )
self-join:select t1.ID1
, t1.ID2
, t1.Property1
, t1.Property2
, t1.myDate
from myTable t1
inner
join myTable t2
on t1.ID1 = t2.ID1
group
by t1.ID1
, t1.ID2
, t1.Property1
, t1.Property2
, t1.myDate
having t1.myDate = min(t2.myDate)