Generally, I prefer

SELECT *
FROM tbl_x a
WHERE NOT EXISTS(
SELECT * FROM tbl_x b
WHERE a.link2case = b.link2case
AND b.filedate > a.filedate)

for this kind of problem. A simple and easily understandable solution that works with all versions of Firebird that I've used (though the query gets slightly more complicated if you want only one row when there can be several records with the same filedate for each link2case).

As an alternative, with new Firebird versions you can use:

WITH TMP(link2case, filedate) as
(SELECT link2case, max(filedate)
FROM tbl_x)

SELECT x.*
FROM tbl_x x
JOIN tmp t
ON x.link2case = t.link2case
AND x.filedate = t.filedate

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: 26. februar 2010 21:04
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Should I use a temporary table for this query? General SQL help needed.

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