Quantity by first date SQL

Question:

SELECT 
store.odyid
 ,g.FID
 ,sds.QUANTITY
 ,min(convert(date,doc1.DOC_DATE)) as min_date
FROM [store.move] AS m
INNER JOIN   [STORE.DOC] AS doc1 ON m.id_doc = doc1.ID_DOC
INNER JOIN  [store.move.spec] AS sm ON m.id_doc = sm.id_doc
INNER JOIN   [STORE.DOC.SPEC] AS sds ON sm.id_doc_spec = sds.ID_DOC_SPEC
INNER JOIN   artikul_full ON sm.id_artikul_new = artikul_full.ID_artikul
INNER JOIN   STORE ON m.id_store_dest = STORE.ID_STORE 
inner join [GOODS] g on g.ID_GOODS = artikul_full.ID_GOODS

WHERE doc1.DOC_DATE between @sd and @ed
and doc1.IS_DONE=1 
and store.STORE_TYPE = 4 
and store.odyid < 950
group by store.odyid
,g.FID
,sds.QUANTITY
order by store.odyid , g.FID

it returns a result like this

----------
odyid   FID QUANTITY    min_date
----------
3   1   4   2010-05-31
3   1   6   2010-04-14
3   50  2   2010-08-18
3   50  3   2010-04-07
3   50  4   2010-11-11
3   50  6   2010-09-29
3   50  9   2010-10-27

How to make it return the following result

----------
odyid   FID QUANTITY    min_date
----------
3   1   6   2010-04-14
3   50  3   2010-04-07

That is, make sure that the minimum date gives out the quantity

Answer:

Idea to use window functions

with [data] AS (
  SELECT 
store.odyid
 ,g.FID
 ,sds.QUANTITY
 ,min(convert(date,doc1.DOC_DATE)) as min_date
FROM [store.move] AS m
INNER JOIN   [STORE.DOC] AS doc1 ON m.id_doc = doc1.ID_DOC
INNER JOIN  [store.move.spec] AS sm ON m.id_doc = sm.id_doc
INNER JOIN   [STORE.DOC.SPEC] AS sds ON sm.id_doc_spec = sds.ID_DOC_SPEC
INNER JOIN   artikul_full ON sm.id_artikul_new = artikul_full.ID_artikul
INNER JOIN   STORE ON m.id_store_dest = STORE.ID_STORE 
inner join [GOODS] g on g.ID_GOODS = artikul_full.ID_GOODS

WHERE doc1.DOC_DATE between @sd and @ed
and doc1.IS_DONE=1 
and store.STORE_TYPE = 4 
and store.odyid < 950
group by store.odyid
,g.FID
,sds.QUANTITY
),
[data1] AS (
select 
  [data].*, 
  ROW_NUMBER() OVER(ORDER BY odyid , QUANTITY desc) AS rowNumber    
 from [data]
)
select * from [data1] where rowNumber = 1
Scroll to Top