questions/3-ranking-without-window-function/

[utterances-bot]

שאילתות מהחיים - דירוג שורות בלי פונקצית חלון

תרגילי SQL משגרת העבודה של אנליסט

https://sql.amitgrinson.com/questions/3-ranking-without-window-function/

[xLiad]

SELECT year, category, amount, (SELECT COUNT(*)
FROM sales_ag S2
WHERE S1.amount < S2.amount) + 1 AS "Rank"
FROM sales_ag S1
ORDER BY Rank

[xLiad]

ובתור Dense_rank

SELECT year, category, amount, (SELECT COUNT(DISTINCT amount)
FROM sales_ag S2
WHERE S1.amount < S2.amount) + 1 AS "Rank"
FROM sales_ag S1
ORDER BY Rank

[mirishk]

select s.* ,
(select count(distinct amount)
from sales_ag s2
where s.year = s2.year
and s2.amount >= s.amount
)as custom_dense_rank
from sales_ag s
order by year, amount desc