I have a dynamic table consist of more than 500,000 records. I want to select records which are recently added.
epc toX toY observTime
000000000000000000000041 2.41 -6.22 2018-01-12 17:04:00.070
000000000000000000000041 3.45 -7.26 2018-01-12 17:14:30.090
000000000000000000000041 2.67 -6.48 2018-01-12 17:27:00.073
000000000000014028924001 0.73 -1.86 2018-01-12 17:31:30.083
000000000000014028924001 0.64 -1.89 2018-01-12 17:42:00.077
11110001 1.96 -4.73 2018-01-12 17:31:30.083
11110001 2.02 -4.68 2018-01-12 17:42:00.083
11110001 2.1 -4.62 2018-01-12 17:21:00.090
I need output like this
epc toX toY observTime
000000000000000000000041 2.67 -6.48 2018-01-12 17:27:00.073
000000000000014028924001 0.64 -1.89 2018-01-12 17:42:00.077
11110001 2.02 -4.68 2018-01-12 17:42:00.083
In short, I want to select record which is recent one according to 'observTime'.
I mentioned the dynamic table which means data is getting stored in table periodically and I need to select record which has recent observTime
What I have tried:
SELECT epc, toX, toY, toFacility, toFloor, toZone, observTime
FROM dbo.tbl_ItemHistoryInfo AS t1
WHERE ((SELECT COUNT(*)
FROM dbo.tbl_ItemHistoryInfo AS t2
WHERE (epc = t1.epc) AND (observTime > t1.observTime)) = 0)
I created a view using this query. But I am not getting recent records.
Can you please help me by providing a query in which I will get recent records? As well as I have to consider time complexity of the query. I have more than 500,000 records. So whether this will be the good solution for it?