Posted by Erland Sommarskog on 10/01/89 11:41

Bill Karwin (bill@karwin.com) writes:
> "Thomas Kellerer" <WVIJEVPANEHT@spammotel.com> wrote in message
> news:46petmFc69qnU1@individual.net...
>> SELECT *
>> FROM playback_log a
>> WHERE a.event_id = (select min(event_id) from playback_log b
>> where a.field1 = b.field1)
>
> Here's a similar possibility, without using a correlated subquery:
>
> SELECT a.*
> FROM playback_log AS a
> WHERE a.event_id IN (
> SELECT MIN(b.event_id)
> FROM playpack_log AS b
> GROUP BY b.field1, b.field2, b.field3, ...)
>
> What I've seen missing in the several solutions proposed is any use of
> GROUP BY. You'll need to GROUP BY all the fields of the table _except_
> for event_id.

One more variation, using a derived table:

SELECT a.*
FROM playback_log AS a
JOIN (SELECT MIN(event_id)
FROM playback_log
GROUP BY field1, field2, ...) AS b ON a.event_id = b.event_id




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]