SQL how to select date from 1 - many

Question

In SQL 2005 stored proc I need to run a query that contains a 1-M. I need to return only 1 of the Many table the one with the earliest date.

I have looked at In SQL how do I write a query to return 1 record from a 1 to many relationship?

and SQL conundrum, how to select latest date for part, but only 1 row per part (unique)

But I am not sure what's the best solution in my case as I am also doing a Insert Into temp table and using dynamic sorting and paging.

Here is my SQL. What I want is to return many rows of Foo, but only the earliest b.CreatedDate between the start and end data paramaters I pass in where there is normally about 5 rows in Bar for each Foo.

DECLARE      @StartDate datetime 
 DECLARE     @EndDate datetime 

INSERT INTO @Results
          SELECT distinct
                f.Name,
                 f.Price
                b.CreatedDate ,
                // loads more columns removed for brevity   
          FROM
                foo f
            join bar b on f.Id = b.fooId
               // loads more table removed for brevity 
          WHERE
                (@x is null OR f.Id = @x)
            AND (@Deal is null OR f.IsDeal = @Deal)
            AND (@StartDate is null OR sd.SailingDate >= @StartDate)
            AND (@EndDate is null OR sd.SailingDate <= @EndDate)
                  // loads more filters removed for brevity 

        declare @firstResult int, @lastResult int
        set @firstResult = ((@PageNumber-1) * @ItemsPerPage) + 1;
        set @lastResult = @firstResult + @ItemsPerPage;
        select @TotalResults = count(1) from @Results;

        WITH ResultItems AS
        (
            SELECT *, ROW_NUMBER() OVER (
                ORDER BY
                CASE    WHEN @SortBy = 'priceLow' THEN Price END ASC,
                CASE    WHEN @SortBy = 'Soonest' THEN CreatedDate END ASC,
                CASE    WHEN @SortBy = 'priceHigh' THEN Price END DESC
            ) As  RowNumber
            FROM @Results r
        )
        SELECT * from ResultItems
        WHERE RowNumber >= @firstResult AND RowNumber < @lastResult
        ORDER BY
        CASE
            WHEN @SortBy = 'priceHigh' THEN (RANK() OVER (ORDER BY Price desc))
            WHEN @SortBy = 'priceLow' THEN (RANK() OVER (ORDER BY Price))
            WHEN @SortBy = 'Soonest' THEN (RANK() OVER (ORDER BY CreatedDate )) 
        END

This query as is will return multiple 'b.CreatedDate' instead of just the earliest one between my Filters

Update So I want to See If my 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 would be

1, Hello,2011-2-4
2, There,  2012-11-2

So you want to see the same createdate on multiple rows that may or may not have that createdate, as long as it is the earliest one? — JNK, May 11 '11 at 12:47
@Duncan - he would need to change the entire structure of the query to include a `GROUP BY` and other aggregates as well for that. — JNK, May 11 '11 at 12:57
@Daveo - the CTE solution is a good one, is there a problem with it? — JNK, May 11 '11 at 12:58
@Duncan -I cannot use min. If I do it give an error Column 'f.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And I do not want to group every single column as there is over 20 of them not shown here — Daveo, May 11 '11 at 13:00
@JNK - No problem with it - I just got it working 2 seconds ago. I just had never used one before and was not sure if it played nicely with the rest of my code. But after just some trial and error, it seems to do the job well. Thanks for the feedback — Daveo, May 11 '11 at 13:02
@Daveo - CTEs are great for circumstances like this. You could potentially join a subselect as well but I think a CTE will probably perform better. — JNK, May 11 '11 at 13:03

score 0 · Accepted Answer · edited May 23 '17 at 11:47

I think I just got it working by adding a CTE to the top of my query

;with cteMinDate as (
    select FooId, min(CreatedDate) As CreatedDate
    from Bar            WHERE
          (@StartDate is null OR CreatedDate>= @StartDate)
          AND (@EndDate is null OR CreatedDate<= @EndDate)
        group by FooId
)

Same as shown here SQL conundrum, how to select latest date for part, but only 1 row per part (unique). Doing this allows me to remove the date query part from my main query and only do it once in the CTE

SQL how to select date from 1 - many

1 Answers1