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
 SQL Server Development (2000)
 Query Grouping/Aggregation

Author  Topic 

cardinco
Starting Member

5 Posts

Posted - 2007-01-30 : 10:30:29
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!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 10:38:39
[code]
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
[/code]


KH

Go to Top of Page

cardinco
Starting Member

5 Posts

Posted - 2007-01-30 : 11:15:19
Thanks for the quick response. Most excellent. Thank you!
Go to Top of Page
   

- Advertisement -