# How to use a Over/Partion clause to select a dynamic minimum row?

• ### Question

• What I am attempting to do is when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty, I want a new column created showing the minimum date(sorels.fduedate) from that criteria labeled as EarliestDate.

This the expected result that I'm looking for

```CREATE TABLE ExpectedResult (
sorelsfsono INT
, sorelsfjoqty int
,sorelsfbook int
, sorelsfbqty int
, fshipbook int
, fshipmake int
, sorelsfduedate date
, EarliestDate date
);

Insert into ExpectedResult
Values ('039069',0,1,0,1,0,0,'2019-03-19','2019-04-03'),
('039069',1,0,0,1,0,0,'2019-03-19','2019-04-03'),
('039069',0,1,0,1,0,0,'2019-03-19','2019-04-03'),
('039069',0,1,0,1,0,0,'2019-03-25','2019-04-03'),
('039069',0,0,0,1,0,0,'2019-04-03','2019-04-03'),
('039069',0,1,0,0,0,0,'2019-04-10','2019-04-03'),
('039069',0,1,0,0,0,0,'2019-04-17','2019-04-03')```
This is my actual query
```SELECT
sorels.fsono,
sorels.fjoqty ,
sorels.fbook ,
sorels.fbqty
fshipbook,
fshipmake,
sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered

FROM m2mdata35.dbo.sorels

INNER JOIN soitem on sorels.fsono+sorels.finumber=soitem.fsono+soitem.finumber
INNER JOIN somast on sorels.fsono=somast.fsono
LEFT JOIN cspopup ON 'SHIPVIA             '+somast.fshipvia=cspopup.fcpopkey+cspopup.fcpopval
LEFT JOIN inmast on soitem.fpartno+soitem.fpartrev=inmast.fpartno+inmast.frev
LEFT JOIN incros on inmast.fpartno+inmast.frev=incros.fpartno+incros.fcpartrev
LEFT JOIN jomast on sorels.fstatus=jomast.fjobno
LEFT JOIN joitem on jomast.fjobno=joitem.fjobno
LEFT JOIN jodrtg on jomast.fjobno=jodrtg.fjobno
LEFT JOIN inwork on jodrtg.fpro_id=inwork.fcpro_id
LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno
LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid

WHERE LEFT(somast.fstatus,1)<>'C'
and fsocoord<>'IFP' and fsocoord<>'711'
and somast.fsono='039069'
Order By sorels.fsono, sorels.fduedate```

Tuesday, April 23, 2019 5:02 PM

• Hi David9501,

```;with cte1 as (
SELECT
sorels.fsono,
sorels.fjoqty ,
sorels.fbook ,
sorels.fbqty,
fshipbook,
fshipmake,
sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered,
min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end ) as EarliestDate,
Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end as [value]
FROM m2mdata35.dbo.sorels

INNER JOIN soitem on sorels.fsono+sorels.finumber=soitem.fsono+soitem.finumber
INNER JOIN somast on sorels.fsono=somast.fsono
LEFT JOIN cspopup ON 'SHIPVIA             '+somast.fshipvia=cspopup.fcpopkey+cspopup.fcpopval
LEFT JOIN inmast on soitem.fpartno+soitem.fpartrev=inmast.fpartno+inmast.frev
LEFT JOIN incros on inmast.fpartno+inmast.frev=incros.fpartno+incros.fcpartrev
LEFT JOIN jomast on sorels.fstatus=jomast.fjobno
LEFT JOIN joitem on jomast.fjobno=joitem.fjobno
LEFT JOIN jodrtg on jomast.fjobno=jodrtg.fjobno
LEFT JOIN inwork on jodrtg.fpro_id=inwork.fcpro_id
LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno
LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid

WHERE LEFT(somast.fstatus,1)<>'C'
and fsocoord<>'IFP' and fsocoord<>'711'
and somast.fsono='039069'
),cte2 as (
select distinct fsono,EarliestDate from cte1 where [value]=1
)
select a.fsono sorelsfsono,a.fjoqty sorelsfjoqty,a.fbook sorelsfbook,a.fbqty sorelsfbqty,
from cte1 a join cte2 b on a.fsono=b.fsono```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Thursday, April 25, 2019 4:13 PM
Wednesday, April 24, 2019 3:26 AM

### All replies

• This?

```SELECT
sorels.fsono,
sorels.fjoqty ,
sorels.fbook ,
sorels.fbqty
fshipbook,
fshipmake,
sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered
,
Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then
min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end Order by sorels.fduedate )
else sorels.fduedate
end as EarliestDate

FROM m2mdata35.dbo.sorels
--where```

Tuesday, April 23, 2019 5:43 PM
• This?

```SELECT
sorels.fsono,
sorels.fjoqty ,
sorels.fbook ,
sorels.fbqty
fshipbook,
fshipmake,
sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered
,
Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then
min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end Order by sorels.fduedate )
else sorels.fduedate
end as EarliestDate

FROM m2mdata35.dbo.sorels
--where```

Almost, I want all of the rows in the EarliestDate column to have the same date 2019-04-03
Tuesday, April 23, 2019 6:56 PM
• Remove sorels.fsono column in partition by.
Tuesday, April 23, 2019 7:27 PM
• That did not work. I received the same results.
Tuesday, April 23, 2019 8:22 PM
• Hi David9501,

```;with cte1 as (
SELECT
sorels.fsono,
sorels.fjoqty ,
sorels.fbook ,
sorels.fbqty,
fshipbook,
fshipmake,
sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered,
min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end ) as EarliestDate,
Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end as [value]
FROM m2mdata35.dbo.sorels

INNER JOIN soitem on sorels.fsono+sorels.finumber=soitem.fsono+soitem.finumber
INNER JOIN somast on sorels.fsono=somast.fsono
LEFT JOIN cspopup ON 'SHIPVIA             '+somast.fshipvia=cspopup.fcpopkey+cspopup.fcpopval
LEFT JOIN inmast on soitem.fpartno+soitem.fpartrev=inmast.fpartno+inmast.frev
LEFT JOIN incros on inmast.fpartno+inmast.frev=incros.fpartno+incros.fcpartrev
LEFT JOIN jomast on sorels.fstatus=jomast.fjobno
LEFT JOIN joitem on jomast.fjobno=joitem.fjobno
LEFT JOIN jodrtg on jomast.fjobno=jodrtg.fjobno
LEFT JOIN inwork on jodrtg.fpro_id=inwork.fcpro_id
LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno
LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid

WHERE LEFT(somast.fstatus,1)<>'C'
and fsocoord<>'IFP' and fsocoord<>'711'
and somast.fsono='039069'
),cte2 as (
select distinct fsono,EarliestDate from cte1 where [value]=1
)
select a.fsono sorelsfsono,a.fjoqty sorelsfjoqty,a.fbook sorelsfbook,a.fbqty sorelsfbqty,
from cte1 a join cte2 b on a.fsono=b.fsono```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Thursday, April 25, 2019 4:13 PM
Wednesday, April 24, 2019 3:26 AM
• ```SELECT
sorels.fsono,
sorels.fjoqty ,
sorels.fbook ,
sorels.fbqty
fshipbook,
fshipmake,
sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered
,

min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end Order by sorels.fduedate )  as EarliestDate

FROM m2mdata35.dbo.sorels
--where```
Wednesday, April 24, 2019 3:48 AM
• This works well for my original question but I'm losing twos of the total rows in the final output.
Wednesday, April 24, 2019 3:50 PM
• It is not possible because the query didn't change your filter condition at all.
Wednesday, April 24, 2019 3:57 PM
• Hi David9501,

I tried to do an example using above script and it will work well. Here is the example .

```If Object_ID('test','U') Is Not Null Drop Table test
go
CREATE TABLE test (
fsono INT
,fjoqty int
,fbook int
,fbqty int
,fshipbook int
,fshipmake int
,fduedate date
);

Insert into test
Values ('039069',0,1,0,1,0,0,'2019-03-19'),
('039069',1,0,0,1,0,0,'2019-03-19'),
('039069',0,1,0,1,0,0,'2019-03-19'),
('039069',0,1,0,1,0,0,'2019-03-25'),
('039069',0,0,0,1,0,0,'2019-04-03'),
('039069',0,1,0,0,0,0,'2019-04-10'),
('039069',0,1,0,0,0,0,'2019-04-17')

;with cte1 as (
SELECT
fsono,
fjoqty ,
fbook ,
fbqty,
fshipbook,
fshipmake,
fjoqty + fbook + fbqty as totalordered,
min(fduedate ) Over(Partition by fsono, Case when fshipbook+fshipbuy+fshipmake <> fjoqty + fbook + fbqty then 1 else 0 end ) as EarliestDate,
Case when fshipbook+fshipbuy+fshipmake <> fjoqty + fbook + fbqty then 1 else 0 end as [value]
FROM test
),cte2 as (
select distinct fsono,EarliestDate from cte1 where [value]=1
)
select a.fsono sorelsfsono,a.fjoqty sorelsfjoqty,a.fbook sorelsfbook,a.fbqty sorelsfbqty,
from cte1 a join cte2 b on a.fsono=b.fsono
/*
sorelsfsono sorelsfjoqty sorelsfbook sorelsfbqty fshipbook   fshipbuy    fshipmake   totalshipped totalordered EarliestDate
----------- ------------ ----------- ----------- ----------- ----------- ----------- ------------ ------------ ------------
39069       0            1           0           1           0           0           1            1            2019-04-03
39069       1            0           0           1           0           0           1            1            2019-04-03
39069       0            1           0           1           0           0           1            1            2019-04-03
39069       0            1           0           1           0           0           1            1            2019-04-03
39069       0            0           0           1           0           0           1            0            2019-04-03
39069       0            1           0           0           0           0           0            1            2019-04-03
39069       0            1           0           0           0           0           0            1            2019-04-03
*/

```

If above sample doesn’t satisfy your requirement, please share us your table structure and some sample data along with your expected result. So that we’ll get a right direction and make some test.

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, April 25, 2019 3:18 AM