Problem with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with a query

Author

Topic

roypython
Starting Member

7 Posts

Posted - 2008-03-07 : 00:40:24

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-07 : 00:50:20

SELECT RequestID, MAX(Update_Date) AS Update_Date
FROM AP_request
GROUP BY RequestID

If you want other columns, you can use the above query as a derived table to join back to. Like this:


SELECT r.RequestID, r.Update_Date, ...
FROM AP_request r
INNER JOIN
(
	SELECT RequestID, MAX(Update_Date) AS Update_Date
	FROM AP_request
	GROUP BY RequestID
) t
ON r.RequestID = t.RequestID AND r.Update_Date = t.Update_Date
ORDER BY r.RequestID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 00:51:45

Select [AP_RequestID], max(update_date) as MaxDate
FROM AP_Request
Group by [AP_requestID]

You have overcomplicated it.

edit: tara and I posted at the same time...LO

Poor planning on your part does not constitute an emergency on my part.

roypython
Starting Member

7 Posts

Posted - 2008-03-07 : 03:43:28

Thank you Tara, dataguru.
I will try what you have suggested.
Do you know why my version of the query does not work??
Thanks,
Roy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-07 : 03:47:32

quote:
Originally posted by roypython

Thank you Tara, dataguru.
I will try what you have suggested.
Do you know why my version of the query does not work??
Thanks,
Roy

Because you're just checking for all records with maximum update_date rather than the ones for each request id. SO what you will get will be a list of records which will have a date equal to maximum value of update date without looking at request id field at all!

roypython
Starting Member

7 Posts

Posted - 2008-03-10 : 23:31:16

Thank you Tara,dataguru1971 and visakh16.
Problem solved.
I appreciate your kind help.

Kind wishes
Roy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources