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,
SnigdhaYou can reverse the order and just select the first record:
SELECT TOP 1 *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo DESC|||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
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 unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
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
