Brightlane's pricing analyst is comparing how three position-assignment methods treat tied prices.
Write a query to return the ID, name, and price of every product, plus three position values for each row: a unique sequential position (row_num), a tie-sharing position with gaps after ties (rnk), and a tie-sharing position without gaps (dense_rnk). All three positions are ordered by price in descending order. Sort the final result by price in descending order.
Assumptions:
- For all three position columns, position
1corresponds to the highestpriceand the value increases aspricedecreases. row_numis unique for every product. If two products share aprice, they receive consecutiverow_numvalues in some order.rnkassigns the same value to every product sharing aprice; the next value after a tied set skips ahead by the count of tied products.dense_rnkassigns the same value to every product sharing aprice; the next value after a tied set is always exactly one higher.- The final result is sorted by
pricein descending order.
Output:
- One row per product, with columns
id,name,price,row_num,rnk, anddense_rnk. Sorted bypricein descending order.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
name,
price,
ROW_NUMBER() OVER (
ORDER BY
price DESC
) AS row_num,
RANK() OVER (
ORDER BY
price DESC
) AS rnk,
DENSE_RANK() OVER (
ORDER BY
price DESC
) AS dense_rnk
FROM
products
ORDER BY
price DESCThe shape
Running ROW_NUMBER, RANK, and DENSE_RANK over the same ORDER BY price DESC window puts the three functions side by side on every row, which makes their tie behavior visible without leaving any of it to inference. The values agree on tie-free data and diverge exactly at ties, in the patterns each function defines.
Clause by clause
SELECT id, name, price, ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num, RANK() OVER (ORDER BY price DESC) AS rnk, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnkreturns each product's identifying columns plus three ranking columns. All three windows share the sameORDER BY price DESC, so the row population each function ranks is identical; the only difference is how ties are handled.FROM productsreads every product.ORDER BY price DESCat the end sorts the result set itself from most to least expensive, which makes the three ranking columns read in order down the screen. This is the query's outerORDER BY, not the window's; the two are independent.
Why all three side by side and not a single function
Comparing the three on a single output is the cleanest way to expose what each one does at a tie. ROW_NUMBER always returns distinct integers, so two products tied at the same price will show different row_num values arbitrarily. RANK gives them the same rnk and then jumps past the next integer. DENSE_RANK gives them the same dense_rnk and continues to the next consecutive integer. Putting the three columns next to price lets the reader see the tie and verify the divergence rather than reason about it abstractly.
The trap
The query has two ORDER BY clauses and they do different jobs. The ORDER BY price DESC inside each OVER (...) defines the window's sort, which determines how the three ranking functions assign their integers. The ORDER BY price DESC at the end of the query is the result-set sort, which controls only the order in which rows are printed. Removing the outer ORDER BY would not change a single ranking value; it would only scramble the row order in the output. Removing the inner one would break the rankings entirely. The two clauses look identical and are not interchangeable.
You practiced three ordered-window position functions side by side — ROW_NUMBER, RANK, and DENSE_RANK over the same ORDER BY produce three different position values whose only divergence is in how they handle tied rows.
