Subject Should I use a temporary table for this query? General SQL help needed.
Author samwalker04
I am using Interbase 7.5 and I am new to Interbase, so please bear with me.

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.

Of course, ideally, I would structure my query to only return those two rows in the first place. But I can't think of a way to do that. So my next thought is to use a temporary table to store the resultset, then scan through it with a cursor and weed out the bad data. Something like:

-for the entire result set
-if link2case value already exists in previous rows scanned
-if filedate > filedate for existing record
-delete previous row

So when that's finished, I've got only the most recently filed object for each single case.

My problem is, I don't know how to create a temporary table or a cursor in Interbase. I have used MS SQL Server in the past, and it was pretty simple:

create table #temp(id,whatever)
insert into #temp(select id,whatever from tbl_x)
declare cursor

I have tried and failed to find any useful documentation on these topics specifically for Interbase. Hopefully someone here can help me out. Or maybe even give me an idea on how to change my query so I don't have to use a stored procedure or any temp tables or cursors in the first place.