sql server - SQL how to select date from 1 - many -

in sql 2005 stored proc need run query contains 1-m. need return 1 of many table 1 earliest date.

i have looked @ in sql how write query return 1 record 1 many relationship?

and sql conundrum, how select latest date part, 1 row per part (unique)

but not sure what's best solution in case doing insert temp table , using dynamic sorting , paging.

here sql. want return many rows of foo, earliest b.createddate between start , end data paramaters pass in there 5 rows in bar each foo.

declare      @startdate datetime   declare     @enddate datetime   insert @results           select distinct                 f.name,                  f.price                 b.createddate ,                 // loads more columns removed brevity                              foo f             join bar b on f.id = b.fooid                // loads more table removed brevity                            (@x null or f.id = @x)             , (@deal null or f.isdeal = @deal)             , (@startdate null or sd.sailingdate >= @startdate)             , (@enddate null or sd.sailingdate <= @enddate)                   // loads more filters removed brevity           declare @firstresult int, @lastresult int         set @firstresult = ((@pagenumber-1) * @itemsperpage) + 1;         set @lastresult = @firstresult + @itemsperpage;         select @totalresults = count(1) @results;          resultitems         (             select *, row_number() on (                 order                 case    when @sortby = 'pricelow' price end asc,                 case    when @sortby = 'soonest' createddate end asc,                 case    when @sortby = 'pricehigh' price end desc             )  rownumber             @results r         )         select * resultitems         rownumber >= @firstresult , rownumber < @lastresult         order         case             when @sortby = 'pricehigh' (rank() on (order price desc))             when @sortby = 'pricelow' (rank() on (order price))             when @sortby = 'soonest' (rank() on (order createddate ))          end  

this query return multiple 'b.createddate' instead of earliest 1 between filters

update want see if source data is:

foo ___ 1  , hello 2  , there  boo ___ 1, 1,   2011-2-4 2, 1,   2011-3-6 3, 1,   2012-12-21 4, 2,   2012-11-2 

the result be

1, hello,2011-2-4 2, there,  2012-11-2 

i think got working adding cte top of query

;with ctemindate (     select fooid, min(createddate) createddate     bar                      (@startdate null or createddate>= @startdate)           , (@enddate null or createddate<= @enddate)         group fooid ) 

same shown here sql conundrum, how select latest date part, 1 row per part (unique). doing allows me remove date query part main query , once in cte