distinct query

Hello,
If I have data such as:
col1 col2 col3 col4(date)
1 2 3 01/11/2005
1 2 3 02/11/2005
1 2 3 03/11/2005
1 1 2 04/11/2005
1 1 2 05/11/2005
1 1 2 06/11/2005
How can I select so that the results are
1 2 3 01/11/2005
1 1 2 04/11/2005
ie, cols 1 to 3 distinct are included and the earliest date
Thank you.
S.soc wrote on Tue, 23 May 2006 17:30:38 +0100:

> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
How about
SELECT col1, col2, col3, MIN(col4) FROM data GROUP BY col1, col2, col3
you'll need to specify a sort order though, which you didn't in your post.
Dan|||You can do this with a correlated sub query:
select a.Col1, a.Col2, a.Col3, a.Col4
from SomeTable a
where col4 = (
select min(b.col4)
from SomeTable b
where b.Col1 = a.Col1
and b.Col2 = a.Col2
and b.Col3 = a.Col3
)
You can also use min in the main query, and group by the first 3 columns.
This will not work if you choose to select an additional (non-key) column,
but the first query above will. Performance wise you will have to try both
ways, but I would expect the above query to perform better, assuming you
have an index on (Col1, Col2, Col3, Col4).
Select Col1, Col2, Col3, min(Col4)
from SomeTable
group by Col1, Col2, Col3
"soc" <zxc0@.yahoo.com> wrote in message
news:Oh2i7YofGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
>
>|||Thank you!
"soc" <zxc0@.yahoo.com> wrote in message
news:Oh2i7YofGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
>
>