Crystal Reports: Showing first row for one field, but sorting on a different field

Question

I have a report that shows three rows per item number with different transaction dates (itemnum, transdate). My issue is either the query or Crystal Reports. I want to sort on another field called UnusedMonths desc, but want to continue showing the most recent row for each ItemNum. I am able to hide the older rows in Section Expert:

   Previous ({ItemHistory.ItemNum}) = ({ItemHistory.ItemNum})

If I don't group ItemNum first, duplicate ItemNum will appear in the report. How do I fix this?

Answer 1

One option is to only select the rows with the latest transdate per itemnum. You can do this by creating a SQL Expression, let's call it {%MaxDate}, like:

case when "ItemHistory"."ItemNum" is null then null
else (select max(transdate)
      from itemhistory
      where itemnum = "ItemHistory"."ItemNum")
end

and then adding the following line to your report's selection formula:

{ItemHistory.TransDate} = {%Maxdate}