Rank equivalent Query in MapD


#1

Hi ,
Does MapD have a rank or rowid equivant query ?


#2

Hi @ujjwal009,

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.

Regards,
Veda


#3

Hi @ujjwal009,

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