Select recent records from table

Question

0.00/5 (No votes)

See more:

I have a dynamic table consist of more than 500,000 records. I want to select records which are recently added.

HTML

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?

Posted 12-Jan-18 7:28am

webmail123

Updated 12-Jan-18 9:36am

v2

Add a Solution

Comments

David_Wimbley 12-Jan-18 13:41pm

Have you not added an order by to your query? If the time column at the end is observTime then you would add ORDER BY observTime DESC

webmail123 12-Jan-18 14:02pm

@David. Thanks for reply. I tried with this one

SELECT epc, toX, toY, toFacility, toFloor, toZone, observTime
FROM dbo.tbl_ItemHistoryInfo AS t1
WHERE ((SELECT COUNT(*) AS Expr1
FROM dbo.tbl_ItemHistoryInfo AS t2
WHERE (epc = t1.epc) AND (observTime > t1.observTime)) = 0 )
order by observTime desc

but still not getting expected result.

ZurdoDev 12-Jan-18 14:05pm

All you said is you want the most recent ones. Simple, ORDER BY your date column. Done.

webmail123 12-Jan-18 14:14pm

I want to avoid duplicates also. You can take a look again at what I needed the output like. I need epc, toX, toY and observTime.

ZurdoDev 12-Jan-18 14:16pm

Just add SELECT DISTINCT to avoid duplicates. I don't know your data so it's a little hard to help much.

webmail123 12-Jan-18 15:19pm

@David.. Sorry for confusion. Actually, I mentioned my input and my expected output in question itself. I will mention it here again.

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

The data mentioned above is my actual data in table.

And what I actually want is mentioned as below:

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

so if you observe I want to select the value of epc, toX, toY and observTime. But it should be the recent one on the basis of observTime. In output I mentioned above the value of epc, toX, toY is selected with the recent observTime

webmail123 12-Jan-18 15:24pm

Sorry about the table output. Column names are epc, toX, toY, observTime.

ZurdoDev 12-Jan-18 16:05pm

OK, see Solution 2.

2 solutions

Solution 1

Try something like

SQL

SELECT TOP 100 * FROM MyTable ORDER BY MyDateColumn DESC

Posted 12-Jan-18 8:06am

OriginalGriff

Comments

webmail123 12-Jan-18 14:16pm

This answer wont avoid the duplicates. It will just sort the data. I don't want to sort the data. I want unique records which are recent ones.

OriginalGriff 12-Jan-18 14:22pm

"Duplicates" you didn't mention in the question, and I rarely remove my tin foil hat to read minds these days...

So add DISTINCT to the query...

webmail123 12-Jan-18 15:13pm

Well, if you can remove your tin foil hat to read the expected output from the given input then you may figure it out.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2018-01-12T09:36:00

It sounds like you want the most recent record for each epc. There are different ways of doing it, here is one way using a derived table:

SQL

SELECT DISTINCT t.epc, t.observtime
FROM table1 t
INNER JOIN (
  -- this derived table will give you the epc and the most recent time
  -- which you can then use to rejoin to your main table to get the rest of the fields
  SELECT epc, MAX(observTime) AS MaxobServTime
  FROM table1 
  GROUP BY epc
) x ON t.epc = x.epc AND t.ovservTime = x.MaxObservTime