SELECT ROWNUM, qt_test.bus_unit, qt_test.sales FROM (SELECT 10 as bus_unit, 2000 sales from dual UNION ALL SELECT 10 as bus_unit, 2500 sales from dual UNION ALL SELECT 10 as bus_unit, 2500 sales from dual UNION ALL SELECT 20 as bus_unit, 2500 sales from dual UNION ALL SELECT 20 as bus_unit, 1500 sales from dual UNION ALL SELECT 20 as bus_unit, 1500 sales from dual UNION ALL SELECT 30 as bus_unit, 4500 sales from dual UNION ALL SELECT 30 as bus_unit, 3000 sales from dual UNION ALL SELECT 30 as bus_unit, 4000 sales from dual ) qt_test;
SELECT qt_test.bus_unit, qt_test.sales, ROW_NUMBER() OVER (PARTITION BY qt_test.bus_unit, qt_test.sales ORDER BY qt_test.bus_unit) Row_Number, RANK() OVER (PARTITION BY qt_test.bus_unit ORDER BY qt_test.sales) rank_bu, RANK() OVER (ORDER BY qt_test.sales) rank_sales, DENSE_RANK() OVER (ORDER BY qt_test.sales) dense_rank_sales, DENSE_RANK() OVER (PARTITION BY qt_test.bus_unit ORDER BY qt_test.sales) dense_rank, PERCENT_RANK() OVER (PARTITION BY qt_test.bus_unit ORDER BY qt_test.sales) pct_rank FROM (SELECT 10 as bus_unit, 2000 sales from dual UNION ALL SELECT 10 as bus_unit, 2500 sales from dual UNION ALL SELECT 10 as bus_unit, 2500 sales from dual UNION ALL SELECT 20 as bus_unit, 2500 sales from dual UNION ALL SELECT 20 as bus_unit, 1500 sales from dual UNION ALL SELECT 20 as bus_unit, 1500 sales from dual UNION ALL SELECT 30 as bus_unit, 4500 sales from dual UNION ALL SELECT 30 as bus_unit, 3000 sales from dual UNION ALL SELECT 30 as bus_unit, 4000 sales from dual ) qt_test;
Back
74111