samwalker04 wrote:

>
> I have a stored procedure that runs a query:
>
> "select serial, filedate, link2case from tbl_x where..."
>
> So I return a dataset that includes something like this:
>
> serial filedate link2case
>
> 1 01/05/10 1000
> 2 01/01/10 1000
> 3 02/15/10 1050
> 4 01/25/10 1000
> 5 02/13/10 1050
>
> Here is my problem. I only want two rows out of this dataset, not all
> five. I want only one row returned per link2case value, and for each
> different link2case value, I only want to return the row with the most
> recent filedate. So I only wanted serial 4 and serial 3 from this dataset.
>

This works in all versions of InterBase and Firebird

create view v_x (filedate, link2case) as
select max (filedate), link2case
from tbl_x group by link2case;

select a.serial, a.filedate, a.link2case
from tbl_x a
join v_x b
on a.filedate = b.filedate and a.link2case = b.link2case;

This procedure works with all versions of both products

create procedure p
returns (serial integer, filedate date, link2case integer)
as
begin
for select max (a.filedate), a.link2case
from tbl_x a group by a.link2case
into :filedate, :link2case
do begin
select max (b.serial)
from tbl_x b
where b.filedate = :filedate and b.link2case = :link2case
into :serial;
suspend;
end
end



This works with Firebird 2.1 and later

select a.serial, a.filedate, a.link2case
from tbl_x a
join (select max (filedate), link2case
from tbl_x group by link2case)
as b (filedate, link2case)
on a.filedate = b.filedate and a.link2case = b.link2case;


Good luck,

Ann