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.
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:Table1PK FK Date--------------------------1 5 1/10/20062 5 1/17/20063 5 1/24/20064 6 6/8/20065 6 6/15/20066 6 6/22/20067 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/20064 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 |
|
|
cardinco
Starting Member
5 Posts |
Posted - 2007-01-30 : 11:15:19
|
Thanks for the quick response. Most excellent. Thank you! |
|
|
|
|
|