0

I had earlier problem with CROSS APPLY, which solved nicely (Using CROSS APPLY).

Here is another part. I want to get one newest CarData row (all columns!) for all those cars which have new line within 24 hours. As I assume, first SELECT should get distinct CarIndexes for all those cars which have updated events within 24 hours, and second SELECT gives newest row. Anyhow I dont know where to put DISTINCT word properly, now I just get the list of CarIndexes instead of CarData rows using this query:

SELECT DISTINCT CD.CarIndex FROM [dvm_data].[dbo].[CarData] CD 
CROSS APPLY (
  SELECT TOP 1 * FROM [dvm_data].[dbo].[CarData] WHERE CarIndex = CD.CarIndex ORDER BY ID DESC) MD
WHERE CD.DatetimeOccurred > (GETDATE()-1)

So, how to make DISTINCT and whole rows work?

Community
  • 1
  • 1
user3608009
  • 21
  • 1
  • 4
  • `distinct *` instead of `DISTINCT CD.CarIndex`? – Giorgi Nakeuri Jun 29 '15 at 15:15
  • distinct * is not working, return huge amount of rows. Another question is solved, but this is different one. – user3608009 Jun 29 '15 at 15:29
  • 1
    You are not selecting the fields you needed, try SELECT DISTINCT CD.CarIndex, MD.* From [dvm_data].[dbo].[CarData] CD ...... – XtremeBytes Jun 29 '15 at 15:39
  • Suggestion by XtremeBytes works neither. The problem is, that I want to pass those DISTINCT car ID's to "SELECT TOP 1 * ...." clause, so maybe this whole query is constructed in wrong way. But I cannot decipher how to make it right... :/ – user3608009 Jun 30 '15 at 08:24

0 Answers0