How do I return only the earliest date in an SQL query but include other columns?

Question

I have a SQL View which looks at a table of data and brings back the results with a few joins and a subquery. The subquery is designed to bring back the period with the earliest date, however, because I try to return a few additional columns in that subquery, I still get mutliple rows. Here is my entire view.

SELECT r.Id, 
       r.Broker, 
       com.EarliestDate AS CommencementStart,
       rp.Frequency,
       rp.Duration,

FROM dbo.Requirement AS r LEFT OUTER JOIN
     (SELECT 
          RequirementId, 
          MIN(Commencement) AS EarliestDate 
      FROM RequirementPeriod 
      GROUP BY RequirementId) 
    AS com ON r.Id= com.RequirementId

I want it to bring back the row with the earliest date only. I can get that to work by using the following SELECT

(SELECT RequirementId, Frquency, Duration, MIN(Commencement) AS EarliestDate FROM RequirementPeriod GROUP BY RequirementId, Frequency, Duration) AS com ON r.Id= com.RequirementId

The problem here is that, if I want to include additional columns in this sub query, as above, I start to get multiple rows because it's grouping by each column given.

Is there a way I can take the earliest date but include duration and frequency without it taking multiple rows or by not using grouping?

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, May 06 '21 at 14:22
Please provide sample data and desired results. It is not clear *which* row you want, — Gordon Linoff, May 06 '21 at 15:44

score 1 · Answer 1 · answered May 06 '21 at 15:46

I suspect that what you want is OUTER APPLY:

SELECT r.Id, r.Broker, 
       rp.Commencement AS CommencementStart,
       rp.Frequency, rp.Duration,
FROM dbo.Requirement r OUTER APPLY
     (SELECT TOP (1) rp.*
      FROM RequirementPeriod rp
      WHERE r.Id = rp.RequirementId
      ORDER BY Commencement
     ) rp;

score 0 · Answer 2 · answered May 06 '21 at 16:31

0

Something like this maybe:

with rp as
(
    SELECT *, row = ROW_NUMBER() OVER(Partition by RequirementId ORDER BY Commencement) from dbo.RequirementPeriod  
)
select *
from dbo.Requirement as r
join rp on (rp.RequirementId = r.id) and (rp.row = 1)

answered May 06 '21 at 16:31

Black Light

2,358
5
27
49

How do I return only the earliest date in an SQL query but include other columns?

2 Answers2