DENSE_RANK window function

[laci-aura]

Hi,
I am in need of basic usage of the DENSE_RANK function as described at https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-dense_rank:

INSERT INTO DenseRankDemo(Val)
VALUES('A'),('B'),('C'),('C'),('D'),('D'),('E');

SELECT
	Val,
	DENSE_RANK () OVER ( 
		ORDER BY Val ) 
	ValRank 
FROM
	DenseRankDemo;

A 1
B 2
C 3
C 3
D 4
D 4
E 5

https://sqlite.org/windowfunctions.html
Could you please point me to implementation of something similar to start with? Or could I somehow execute a text SQL query until the feature is incorporated into sqlite_orm?

[fnc12]

hi @laci-aura . Please check this branch #1479

[fnc12]

it has to work like this

struct DenseRankDemo {
    std::string val;
};

auto storage = make_storage("",
    make_table("DenseRankDemo",
        make_column("Val", &DenseRankDemo::val)));

storage.sync_schema();

storage.insert(DenseRankDemo{"A"});
storage.insert(DenseRankDemo{"B"});
storage.insert(DenseRankDemo{"C"});
storage.insert(DenseRankDemo{"C"});
storage.insert(DenseRankDemo{"D"});
storage.insert(DenseRankDemo{"D"});
storage.insert(DenseRankDemo{"E"});

// SELECT Val, DENSE_RANK() OVER (ORDER BY Val) FROM DenseRankDemo
auto result = storage.select(
    columns(&DenseRankDemo::val,
            dense_rank().over(order_by(&DenseRankDemo::val))));

// result: vector<tuple<string, int>>
// {"A", 1}, {"B", 2}, {"C", 3}, {"C", 3}, {"D", 4}, {"D", 4}, {"E", 5}

[laci-aura]

that was fast, thanks a lot!

[fnc12]

merged into dev. @laci-aura thanks to you!