SQL ROW_NUMBER trick

When working with databases you often com across cases when there are several rows that are differentiated only by date time. Say you have several rows with details about an item sold (or whatever) and a field called 'UPDATE_TIME'. For example there was a data entry mistake and the right answer is the row with the latest update time.

And this is when you use the ROW_NUMBER() OVER(PARTITION BY ...trick. It may get confusing at first, but once you get the gist it feels super easy and natural. In a nutshell you tell SQL the field by which you're gonna partition your data (the ID field, for example) and feed it the field by which it'll order the rows. By doing so you'll get a new field with row numbers for each partitioned group. After that you are free to select the desired row number. In our example it'll be number 1 (which gets you the latest entry by update time).

You can find more on this topic in these posts at stackoverflow:

The quick example:
https://stackoverflow.com/questions/18820104/qualify-clause-in-oracle

The detailed explanation:
https://stackoverflow.com/questions/10477085/oracle-partition-by-and-row-number-keyword