Выведем рейтинг самых дешевых товаров, но дороже 22 000:
SELECT p.name,
rank () over (order by min (pi.price)) AS position,
min (pi.price) AS min_price
FROM purchase_item pi,
product p
WHERE p.product_id = pi.product_id
GROUP BY pi.product_id,
p.name
HAVING min (pi.price) > 22000
ORDER BY min_price
# | name | position | min_price |
---|---|---|---|
1 | Xbox | 1 | 23500.00 |
2 | Холодильник A2 | 2 | 26100.00 |
3 | Nintendo | 2 | 26100.00 |
4 | Слайдер B3 | 4 | 38200.00 |
5 | Моноблок C4 | 5 | 43800.00 |
6 | Ультрабук X5 | 6 | 76800.00 |
7 | Nikon D750 | 7 | 100000.00 |
8 | Lord Nikon 95 | 8 | 1321800000.00 |
Теперь наградим победителей за первые три места:
SELECT *
FROM (SELECT p.name,
rank () over (order by min (pi.price)) AS position,
min (pi.price) AS min_price
FROM purchase_item pi,
product p
WHERE p.product_id = pi.product_id
GROUP BY pi.product_id,
p.name
HAVING min (pi.price) > 22000
) p
WHERE p.position <= 3
ORDER BY p.position
# | name | position | min_price |
---|---|---|---|
1 | Xbox | 1 | 23500.00 |
2 | Холодильник A2 | 2 | 26100.00 |
3 | Nintendo | 2 | 26100.00 |
В результате мы наградили за 1-е место, за два 2-х, а за третье место никого не наградили... Потому что у нас после второго места сразу идет четвертое из-за двух вторых мест.
Чтобы после нескольких вторых мест шло таки третье, нужно функцию rank()
заменить на dense_rank()
.
SELECT p.name,
dense_rank () over (order by min (pi.price)) AS position,
min (pi.price) AS min_price
FROM purchase_item pi,
product p
WHERE p.product_id = pi.product_id
GROUP BY pi.product_id,
p.name
HAVING min (pi.price) > 22000
ORDER BY min_price
# | name | position | min_price |
---|---|---|---|
1 | Xbox | 1 | 23500.00 |
2 | Холодильник A2 | 2 | 26100.00 |
3 | Nintendo | 2 | 26100.00 |
4 | Слайдер B3 | 3 | 38200.00 |
5 | Моноблок C4 | 4 | 43800.00 |
6 | Ультрабук X5 | 5 | 76800.00 |
7 | Nikon D750 | 6 | 100000.00 |
8 | Lord Nikon 95 | 7 | 1321800000.00 |