Getting the last event before a time stamp

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 2008 Forums

Transact-SQL (2008)

Getting the last event before a time stamp

Author

Topic

Ambulare
Starting Member

8 Posts

Posted - 2013-07-02 : 10:01:49

I'm trying to get a query that finds a specific item in a sequence of events which occurs just before an event another table.

My query looks like this:

Select

x.startdate,
y.startdate

from Events1 x
left join Events2 y on x.sharedkey = y.sharedkey and y.EventKey = (select top 1 z.eventkey from Events2 z where z.sharedkey = x.sharedkey and z.startdate < x.startdate order by z.startdate desc)

However, I was surprised to discover that with this syntax, SQL ignores the "where z.stardate < x.startdate" clause and simply returns the last event in table y, even if it occurs after the event in table x.

Does TSQL always do this? Is there a way around it to achieve the result I need?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 10:07:31

Just by looking at your code, I can't see a reason why it would ignore the date condition in the where clause. You might be able to simplify the query like shown below:

SELECT x.startdate,
	y.startdate
FROM
	Events1 x
	OUTER APPLY
	(	SELECT TOP (1) z.startdate FROM Events2 z
		WHERE z.sharedkey = x.sharedkey
		AND z.startdate < x.startdate
		ORDER BY z.startdate DESC
	) y

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-02 : 12:01:37

Can you post sample data that shows SQL ignoring the predicate?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources