Скачать или смотреть How to Select the 3rd Highest Values from a Table in SQL Server

Learn a simple SQL query technique to select the `3rd highest` values from a table in SQL Server, ensuring you understand the steps along the way.
---
This video is based on the question https://stackoverflow.com/q/63100164/ asked by the user 'Siddharth Rawat' ( https://stackoverflow.com/u/1630560/ ) and on the answer https://stackoverflow.com/a/63100213/ provided by the user 'Siddharth Rawat' ( https://stackoverflow.com/u/1630560/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Select 3rd highest values from the table in SQL Server

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Select the 3rd Highest Values from a Table in SQL Server

Selecting specific high values from a database can be crucial for data analysis and reporting. In SQL Server, you might often find yourself needing to extract values such as the 3rd highest price of a product or similar queries. This guide will walk you through the process of how to select the 3rd highest values efficiently using an SQL query.

Understanding the Problem

When dealing with a large dataset, retrieving specific positional values, such as the 3rd highest entry, can be challenging. The usual methods of filtering data might not suffice, especially when there are duplicate values. For example, you want to find the third highest list price from a table of products.

To tackle this, we can use SQL's ranking capabilities combined with ordering and limiting functions.

SQL Query to Fetch the 3rd Highest Value

Let's dissect the SQL query that enables you to retrieve the 3rd highest value efficiently:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Query

Subquery:

The core of the query is a subquery that selects the product_name and list_price from the production.products table.

Ordering: It orders the result by list_price in descending order, meaning the highest prices will come first.

OFFSET and FETCH:

OFFSET 2 ROWS skips the first two entries—effectively leaving you starting from the third entry.

FETCH FIRST 4 ROWS ONLY limits the results to the next four prices (including possible duplicates of the third highest value).

Final Selection:

SELECT TOP 1 WITH TIES is the last step which ensures that if the 3rd highest value has any ties (i.e., multiple entries with the same price), those will also be fetched.

Example Use Case

Imagine you manage an online store and want to see the products whose prices rank third highest. By using this SQL command, not only do you find the third distinct price, but you also receive any products that share that price.

Adjusting the Query for Other Values

You can easily modify the OFFSET value to select other positions:

To get the 2nd highest value, change OFFSET 2 ROWS to OFFSET 1 ROWS.

For the 4th highest value, modify it to OFFSET 3 ROWS accordingly.

Conclusion

Retrieving the 3rd highest values from a table may seem daunting initially, but with the right SQL techniques, it can be simple and straightforward. Use the query provided as a template and modify it for other positional values as needed. By leveraging SQL’s powerful sorting and fetching capabilities, you can efficiently analyze your data and derive meaningful insights.

Happy querying!