Select Record that has the Maximum Date

[dannyyo]

TableA

Date Dept
1/2/09 Order
1/10/09 Build
2/10/09 Ship
Invoice


I have a "TableA" which includes these two columns. I would like to display on my Crystal Reports the latest department on the order. I've looked at using SQL expression, but it error out may be because I'm hitting against an Oracle DB? It seems very easily doable, but so hard.


select Dept, Date
from TableA
where (Date = (select max(Date) from TableA where sonum = xxxxxx1 and soitemnum = 1))
and sonum = xxxxxx1
and soitemnum = 1

This SQL statement gives me exactly what I want to see, but can't seem to translate it to work with Crystal XI.


The result I'd like to see here would be

2/10/09 Ship

 

[Turkbear]

Hi,
Use a Command Object not a direct table link ..

You can use that sql as the command.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could use a command, or you should be able to create a SQL expression like {%maxdate}:

(
select max("Date")
from "TableA" A
where
A."sonum" = 'xxxxxx1' and
A."soitemnum" = 1
)

...assuming sonum is a string, and soitemnum is a number. Then you could use the following in a record selection formula:

{TableA.Date} = {%maxdate} and
{TableA.sonum} = 'xxxxxx1' and
{TableA.soitemnum} = 1

Or you could just omit the SQL expression, and use:
{TableA.sonum} = 'xxxxxx1' and
{TableA.soitemnum} = 1

...and go to report->selection formula->GROUP and enter:
{TableA.Date} = maximum({TableA.Date})

This assumes you want you report to return one record only. If you wanted to do this per some group, you would have to build that into the SQL expression or into the group selection formula.

-LB

 

[dannyyo]

Thanks LB! I used the Group selection and it works great!