Problem with a query - Stored Procedures, Triggers, & Functions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » Problem with a query

Problem with a query

Options

Previous Topic · Next Topic

roypython

Posted: Thursday, March 06, 2008 11:49:17 PM

Rank: Newbie

Joined: 3/6/2008
Posts: 1
Points: 3
Location: nz

Thank you for your kind help.

My table logs records by RequestID(it is not a Primary Key).
There can be more than one record with the same RequestID but with a different Update_Date.

I am trying to get the most recent record (with the latest datetime) for each RequestID.

The query:

SELECT fld1,fld2

FROM AP_request AS T

WHERE

( Update_Date =
(SELECT MAX(Update_Date) AS Expr1
FROM AP_request
WHERE (AP_RequestID = T.AP_RequestID)))

The problem:
For some reason, it’s skipping records. For some RequestID it will return the latest record, but for other RequestID it won’t return records at all.
All the fields I mentioned above are never null.

AP_Update_Date is type of datetime (and not null).

Thank you,
Roy

Scott Whigham

Posted: Friday, March 07, 2008 7:42:26 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hi Roy -

first, the query isn't "skipping records" hehe Smile

Look at it like this: if the AP_request table had 10 rows and only two unique RequestIDs, then the result of this query would be at a *minimum* two rows. It could be 3, 4, or 5 rows if the same RequestID had the same Update_Date and that Update_Date was the MAX(). But let's be clear: the minimum returned number of rows is two - one per each unique RequestId.

Now you said it does not return some RequestIds - no way. This query - the one you pasted - will return every distinct RequestId in the table. Is there perhaps other parts to the query that, for the sake of brevity/clarity, you left out?

The query you posted solved the request to retrieve the most recent record for each RequestId.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.