Cursor versus Temporary Table

Hi All,
I am writing a stored procedure in which I need to read some records from a table which satisfy given condition, fetch the last read record and check its value.

e.g.
SELECT *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo.

I want to read the last record of each TestReqNo and check some values from that row. For this I was thiking of reading the above Select using a cursor and then using FETCH LAST to read the last row into some variable.

But this seems to be a round about way and also i have been reading that cursor will slow the execution. IS there any other better way. Should I use temp table instead, will that be more efficient

Please let me know your suggestions.
Thanks,
Snigdha

Answer this question

Cursor versus Temporary Table

HarryS
You can reverse the order and just select the first record:

SELECT TOP 1 *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo DESC
parcalto
Hey,
Thanks a loooot...
the first query worked perfectly the way I wanted it !!!! :)

There are minor things remaining which I think I should be able to handle.
Thanks a lot once again,
Snigdha
Ecko
Hi,
Thanks a lot for this solution, but there is still a little problem. The above query returns the last record of the last TestRequestNO. But I wanted to the last record of each TestRequestNO. For e.g.:
TestRequestNo TestRequestStateId
TR123  11
TR123  3
TR123  5
TR123  12

TR155  11
TR155  3
TR155  5

TR007  11
TR007  12
TR007  3

Now, out of this orderd set of TestRequestNos I want the last record of each of the TestRequestNo: TR123, TR155, TR007.

Thanks,
Snigdha
Wilke Jansoone

So, something along these lines

USE Northwind

SELECT t1.* FROM [order details] t1
WHERE t1.Quantity=
(SELECT MAX(Quantity) FROM [order details] t2
WHERE t1.orderid=t2.orderid)
ORDER BY t1.orderid

SELECT t1.* FROM [order details] t1 INNER JOIN
(SELECT orderid, MAX(Quantity) AS maxdate FROM [order details] GROUP BY orderid) t2
ON t1.orderid = t2.orderid
AND t1.Quantity = t2.maxdate
ORDER BY t1.orderid
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstutze PASS Deutschland e.V. (http://www.sqlpass.de)
Cursor versus Temporary Table

Answer this question