Does MapD have a rank or rowid equivant query ?
MapD has a concept of a rowid which is a system managed number unique to a row. We use this from Immerse for sampling and point look ups for rendered queries but you can use this in other queries as well.
Example: select rowid, var_a from table limit 10
NOTE: rowid is not documented formally as it is not an official functionality yet and could change in the future.
there aren’t windows function like rank, but you can compute a rank in this way
assuming a table with an id and a value used for the ranking like this
CREATE TABLE test_rank_n ( id_key INTEGER, somevalue INTEGER) 1|10 2|4 3|40 4|10 5|10
you can use a query like that to get a simple rank, but I guess it’s an expensive operation
mapdql> select a.id_key,sum(case when a.somevalue=b.somevalue then 0 else 1 end)+1 from test_rank_n a, test_rank_n b where a.somevalue <= b.somevalue group by 1 order by 2; 3|1 1|2 4|2 5|2 2|5 5 rows returned. Execution time: 9 ms, Total time: 10 ms
applying the same principle you can do a running sum (but it’s limited to a numerical id)
select a.id_key,sum(b.somevalue) from test_rank_n a, test_rank_n b where a.id_key >= b.id_key group by 1order by 1; 1|10 2|14 3|54 4|64 5|74
What do you mean by rowid? If you are looking for a unique identifier of a row in a resultset, there isn’t any.
As Veda already said the rowid of Mapd is a runtime generated the unique identifier for each row in a table; this means that if you join 2 tables you will have 2 rowids completely unrelated each other