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