Really need to use this query - but it has a sub-query in it...

Hi all,

I have the following query in my application and really need to run this as it will help my application in terms of performance and efficiency. I need to get the record that was last entered into the database, and the following seems to do it in SQL Server Desktop, but not in SQL Server Mobile (as it has a Sub-Query). At the moment I have a query that returns all rows (in order) that have a DateCompleted column entered and then just skimming the top row. So i'm infact bringing back hundreds of rows, just so that I can get the first record - completed waste resources if you ask me.

Does anyone know how I can tackle this issue?

SELECT *
FROM tbl_NSP_AnswerSet
WHERE (DateCompleted IS NOT NULL)
AND DateCompleted = (SELECT MAX(DateCompleted) FROM tbl_NSP_AnswerSet)
ORDER BY DateCompleted DES

Thanks

Tryst

Hi

Create a User Define function which will calculate for you the Max datecomleted for that table and then in the where clause compaire the datecompleted.

CREATE FUNCTION GetMaxDateCompleted()
RETURNS int
AS
BEGIN
(SELECT MAX(DateCompleted) FROM tbl_NSP_AnswerSet)
END

AND

SELECT *
FROM tbl_NSP_AnswerSet
WHERE (DateCompleted IS NOT NULL)
AND DateCompleted = dbo.GetMaxDateCompleted()
ORDER BY DateCompleted DES

|||But SQL Server mobile doesn't support Functions (UDF's), does it?

Tryst