select one row/date

[DanHD]

hello

I’ve a sales table with items and the date when they are sold.
Most items are sold multiple times on different days.
How do I write the query with as result the items with the latest day when they were sold?


Dan

 

[PWise]

Select max(selldate),itemno
from tablename
group by itemno

 

[markros]

Assuming you may want to get other fields and you're using SQL 2005 or up, here is another way:

;with cte as (select *, row_number() over (partition by ItemNo order by [Date] Desc) as Rn from Sales)
select * from cte where Rn = 1

Explanations and other solutions you may find in these blogs

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[DanHD]

Hello

Thanks for your input. I checked both queries and they give the same results.
I’ve tried to modified the query. Because not all the items are stored in the transaction table, and I need also to know on which items there isn’t a transaction.

First I created a query with an #temp table and after that I tried to modified the query from Markros.
My own query give (I think//hope) the right results. But the modified query don’t show me the items who are not in the transaction table. I’ve tried the inner, left and right join without results.


My own query:

drop table #datum
select itemcode
, max(docdate) as docdate
into #datum
from oinm
group by itemcode

select * --t1.itemcode, t0.docdate
from #datum t0
right join oitm t1 on t0.itemcode = t1.itemcode
order by t0.itemcode


the 'modified'query from Markros:
with cte as (select *, row_number() over (partition by Itemcode order by [docdate] Desc) as Rn from OINM)
select *
from cte
right outer join OITM on cte.itemcode = OITM.itemcode
where Rn = 1
order by OITM.itemcode

Dan

 

[r937]

you used WHERE, but you shoulda used AND

WITH cte AS 
( SELECT *
       , ROW_NUMBER() OVER 
           ( PARTITION BY itemcode 
                 ORDER BY docdate DESC ) AS rn 
    FROM oinm )
SELECT oitm.*
     , cte.docdate AS latest
  FROM oitm 
LEFT OUTER 
  JOIN cte  
    ON cte.itemcode = oitm.itemcode
   [red]AND[/red] cte.rn = 1
ORDER 
    BY oitm.itemcode

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DanHD]

Thanks very much, this solves the issue.
I get now the same result with the #temp table by mine own solution.
But which method prefers? Both ways use a temp table. Is it because 'Markros' method is quicker to write? Faster for the server? Or more power full?
(just for the ‘learning’ aspect and the future).


Dan

 

[r937]

actually, only your method creates an actual temporary table

a CTE declaration (common table expression) is a convenient, if sometimes the only, way of writing the query

it's similar to a view

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Another version (I prefer LEFT JOIN as easier to understand):

select OTM.*, MD.MaxDate as Latest
from OTM LEFT JOIN (select ItemCode, max(DocDate) as MaxDate
from OINM group by ItemCode) MD on OTM.ItemCode = MD.ItemCode

PluralSight Learning Library

 

[DanHD]

Hi
So far so good.
I tried to build further on the latest method of markros.
I need an extra column from the OINM table: the transtype.
And that gives a problem in the group by, I got too much rows back.
The other method with ‘partition by’ don’t works in the ERP program.

select	t0.itemcode
--		, oinm.transtype ???? how do I get this extra column
		, t1.maxdate as latest
from	oitm t0
		left join
		(select itemcode, max(docdate) as maxdate
		from oinm 
		group by itemcode) t1 on t0.itemcode = t1.itemcode

Dan

 

[markros]

Try then:

select OTM.*, MD.MaxDate as Latest, substring(Compound,11,len(Compound)) as TranType
from OTM LEFT JOIN (select ItemCode, max(DocDate) as MaxDate,

max(convert(char(10),DocDate,112) + TranType) as Compound

from OINM group by ItemCode) MD on OTM.ItemCode = MD.ItemCode

This is if you want to use joins and not the cte and row_number idea.

PluralSight Learning Library