Search code examples
sql-servercross-apply

Using CROSS APPLY

I have table Car with car ID's (smallint), and another table with events related with each car.

Now I want to get latest event for cars selected by certain criteria, but this does not seem to work.

When I have query like this to get the latest event for every car it works ok:

SELECT * FROM [dvm_data].[dbo].[Car] CD 
CROSS APPLY (
  SELECT TOP 1 * FROM [dvm_data].[dbo].[CarData] WHERE CarIndex = CD.ID) MD

On the other hand, when I try to limit cars using WHERE in first SELECT, it no longer works:

SELECT * FROM [dvm_data].[dbo].[Car] WHERE ID > 100 CD 
CROSS APPLY (
  SELECT TOP 1 * FROM [dvm_data].[dbo].[CarData] WHERE CarIndex = CD.ID) MD

In this case I get error message:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'CD'. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'MD'.

The first query in itself works fine:

SELECT * FROM [dvm_data].[dbo].[Car] WHERE ID > 100

What I am missing here? Usually examples using CROSS APPLY do not have WHERE in the first SELECT query, does this mean something?

Another thing, what if I want to use DISTINCT in first query, to find cars that had certain type of event, and then finding latest event for these cars. It would be something like this, but also this does not work:

SELECT DISTINCT ID FROM [dvm_data].[dbo].[CarData] WHERE EventID = 32 CD 
CROSS APPLY (
  SELECT TOP 1 * FROM [dvm_data].[dbo].[CarData] WHERE CarIndex = CD.ID) MD

I suppose this should be very easy, but currently I am missing something...

Solution

  • your where clause is on the wrong position! try this instead:

    SELECT * FROM [dvm_data].[dbo].[Car] CD 
CROSS APPLY (
    SELECT TOP 1 * 
    FROM [dvm_data].[dbo].[CarData] 
    WHERE CarIndex = CD.ID
) MD
WHERE CD.ID > 100