A simple group by query seems impossible

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A simple group by query seems impossible

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2006-07-20 : 12:40:18

I've been fighting with this all day there has to be an easy way to do this in a single query:

FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:

INSPECTION_ID FAC_ID START_DATE
300720040628004 566 2004-07-07
300720030618002 566 2003-06-25
200820021219011 1436 2002-12-19
300720030206002 1458 2003-02-03
300720030206003 1458 2003-02-05
300720030121002 1480 2003-03-25
300720030121003 1480 2005-02-02
200820031230001 1436 2003-12-30
300720040616006 1566 2004-08-26
300720040616001 1566 2001-08-26
300720040616005 1566 2002-08-26
300720040616002 1566 2003-08-26
300720040616003 1566 2004-08-26
300720040616004 1566 2006-08-26
300720040504004 1569 2004-09-10

DESIRED OUTPUT
INSPECTION_ID FAC_ID START_DATE
300720030618002 566 2003-06-25
200820021219011 1436 2002-12-19
300720030206002 1458 2003-02-03
300720030121002 1480 2003-03-25
200820031230001 1436 2003-12-30
300720040616001 1566 2001-08-26
300720040504004 1569 2004-09-10

any help would be greatly appreciated...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 12:58:01

select *
from tbl t
where START_DATE = (select min(START_DATE) from tbl t2 where t2.FAC_ID = t.FAC_ID)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources