Tag: partition

Writing SQL queries can be challenging for complicated tasks. Everything starts with a short * FROM query but the query length may take up to several lines quickly. I have been working on fetching some user records from the database. It seemed easy in the beginning but I had to implement a trick later.

Imagine that you have a table with multiple rows for the same entity. How to pick up the latest record for each entity? It is a pretty common scenario when you want to retrieve the last post in your site or last order of a user.

By using the query below, you can retrieve only the latest record based on a column you specify (It’s the ID column in my example). Make sure to change the column names before running it.

SELECT ID, USER, AMOUNT FROM (SELECT ID, USER, AMOUNT, rank () over (partition by USER order by ID desc) AS rnk FROM ORDERS) x WHERE x.rnk = 1

The sample table and the result of the query above look like this:

Are you looking for a way to launch SQL Server quickly? Check the related post out: Command prompt shortcut to open a Microsoft SQL Server database directly