How to use SQL to Obtain the Latest Timestamp for Identical Row Entries in a Relational Database Table (Toad for Oracle) (Example Provided)

Hi Guys, (A bit of Background ahead and also sorry in advance if I refer to things incorrectly or seem ignorant – I’m still a beginner with SQL)

​

· I work in a Computer Factory that makes Microchips.

· We make these Microchips in batches of 10,000.

· Each of these Microchips can be uniquely identified in a batch by a number (between 1-10,000) assigned to each of them.

· Numbers are assigned to each Microchip based on the order in which they progress through the machines.

· So, the first Microchip to enter the machine is Microchip 1, the next is Microchip 2 and so on.

· The final stage of production that we do involves passing these Microchips through a machine that inspects them with a camera.

· The camera performs 50 separate inspections (Numbered 1 to 50) for each Microchip – each inspection being performed one after the other. (So, a complete batch of 10,000 Microchips will have a total of [10,000 x 50] 500,000 Inspection Records!)

· The last inspection for each Microchip won't necessarily be Inspection Number 50. If the microchip fails at any of the prior inspections, then the machine will reject it (chucks it into a bin). So Microchip 1's final inspection could be 39, Microchip 2's could be 50, Microchip 3's could be 21 etc.

· The Inspection results are then sent to an Oracle Database which I access from my Laptop via a programme called 'Toad'.

​

​

So, I’ve been tasked with Looking at a Particular Batch – Number 1062173601.

To elaborate – I have to Look at the Latest Inspection that the Camera did for Microchips Numbers 1 to 825.

The machine exports all the information to the Toad for Oracle Database in a Table Like Such:

I have truncated the below table to save space.

BATCH_ID	MICROCHIP_NUMBER	INSPECTION_NUMBER	INSPECTION_TIME
1062173601	1	1 OF 50	16DEC21 23:07
1062173601	1	2 OF 50	16DEC21 23:08
1062173601	2	1 OF 50	16DEC21 23:10
1062173601	2	2 OF 50	16DEC21 23:11
1062173601	3	1 OF 50	16DEC21 23:30

So, I know how to filter by the BATCH ID and the Microchip Number using the Filter function:

BATCH_ID = 1062173601

AND MICROCHIP_NUMBER < 826

However, this is giving me all 50 Inspections for all 825 Microchips. (50 x 825 = 41,250 Records!)

Whereas, I just want the Inspection with the Latest Timestamp for Each Microchip. (1 x 825 = 825 Records)

So, if we use the above table and Microchip 1 as an example, I only want the Record at 16DEC21 at 23:08 as it is the latest timestamp and so on for Microchip 2 etc.

Can you please help me structure the SQL Query/ Filter to figure out how to do this?

Many Thanks!