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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query Grouping/Aggregation
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cardinco
Starting Member

5 Posts

Posted - 01/30/2007 :  10:30:29  Show Profile  Reply with Quote
I am brand new to this forum. I can do most basic things in SQL Server, but for some reason I cannot figure out how to do something that is seemingly simple. I have a table that I am trying to query and group the results from based on a minimum date. The catch is that there is a non-unique id, so multiple columns share that id. For example, here is a sample table:

Table1


PK     FK     Date
--------------------------
1      5      1/10/2006
2      5      1/17/2006
3      5      1/24/2006
4      6      6/8/2006
5      6      6/15/2006
6      6      6/22/2006
7      6      6/29/2006


What I want to return in my results set would be the minimum date value grouped by the FK. So, this is what I would want:


PK     FK     Date
--------------------------
1      5      1/10/2006
4      6      6/8/2006


Easy right? I would have thought so, but for some reason I cannot group my results by the FK. It always returns all the rows instead of the rows with the earliest dates that share a common FK.

Any help would be appreciated!

Edited by - cardinco on 01/30/2007 10:34:14

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 01/30/2007 :  10:38:39  Show Profile  Reply with Quote

select t.*
from   table t inner join
       (
            select FK, minDate = min(Date)
            from   table
            group by FK
       ) m
       on  t.FK   = m.FK
       and t.Date = m.minDate



KH

Go to Top of Page

cardinco
Starting Member

5 Posts

Posted - 01/30/2007 :  11:15:19  Show Profile  Reply with Quote
Thanks for the quick response. Most excellent. Thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000