Compare two rows with each other

[boerbende]

Dear readers

I am looking for a way to optimize some queries

My (example) table is
ID I Date1 I Val1
010 I 01-03 I 1
019 I 02-03 I 0
167 I 03-03 I 4
472 I 04-03 I 5
etc, so multiple more records...

I creates a compare SQL between the two records with a nested SQL

SELECT DATE1, VAL1, (SELECT TOP 1 VAL1 from mytable where ID < tbl1.ID ORDER BY ID DESC) as PREV from Mytable as tbl1

Result is exactly what I want

Date1 I Val1 I PREV
01-03 I 1 I
02-03 I 0 I 1
03-03 I 4 I 0
04-03 I 5 I 4

This takes however a long time (even with a good WHERE clausule) to create with many records. Does somebody can advice me to do this more efficient?

Many thanks

Ben

 

[Minty]

How many records are you looking through, are your date fields indexed?

 

[CJ_London]

main thing is to make sure ID field is indexed

and perhaps an alternative

SELECT T.DATE1, T.VAl1, P.VAL1 as PREV
from Mytable T, MyTable P
WHERE P.ID=(SELECT MAX(ID) from mytable where ID < T.ID)

 

[plog]

The technical term for what you want (and are doing) is a 'correlated subquery' (google that). Here's the microsoft page for it in Access: https://msdn.microsoft.com/en-us/library/office/aa217680(v=office.11).aspx

With that said, I see an error with your data and logic:

1. Is ID numeric, because your example data has preceding 0s which implies text. Text sorts different than numbers. Numerically these numbers sort like this: 1, 5, 10, 44, 190. As text those same "numbers" sort like this: 1, 10, 190, 44, 5. This will effect your return value.

2. You probably shouldn't even be ordering your data via ID. You have a date field, don't you really want to determine order on that field?

 

[boerbende]

Thanks minty, cj and plog.
In answer : the users do define the number of records by selecting a period. Even then it is depending on the number of values / measurements in this period.
Yes, i actuallt have a date and time and an using this. But sometimes measurements are on identical date / time, giving an error with the top 1 functionality.
And yes, my id is an integer, not text. This was written wrong in the example. Good observed. I am actually ordering on date and time first, then on id to make the top1 work.



Verstuurd vanaf mijn SM-G800F met Tapatalk